A lot of times you just to show values of rows from the current month compared to other specific time periods, like previous month, ytd, previous YTD, etc. The problem is, how do you determine the current month ? This could be the actual current month (like PerformancePoint 2010 does) but in the most cases this is not sufficient because your last month you have data might be one or more months ago because of your ETL.
In this blog post we create a measure that we can use to filter the data using a filter or slicer so the data in the report will show the the last month where have data.
I start by creating a calculated column in my date table that checks if the month is the current month and returns a Y or N.
To determine if the month is the last month we have data i used the following DAX statement:
=if(MONTH(LASTDATE(ALL(FactInventory[DateKey]))) = DimDate[MonthNumber]
&& year(LASTDATE(ALL(FactInventory[DateKey]))) = DimDate[CalendarYear],"Y","N")The key here is how we determine the last month to have data: LASTDATE(ALL(FactInventory[DateKey]).
Lastdate will check the last date in the column, but because the date table and the fact table have a relationship DAX would automatically use the this to return only rows from the fact table in the current date context. This way we never can check all the fact table rows. We want to overrule the filter context by using ALL() this will give us all the rows from the fact table. LASTDATE(ALL(FactInventory[DateKey]) will give us the one date that is the last date we have a fact for.
Now we can check if the dates from our date table are in the same month and year as the last value of the fact table and return a “Y” when they are and a “N” when they aren’t this will give us the possibility to select all the values from the last month as we can see in this report:
Now we are able to create the following report:
The great thing about is that when because we use a calculated function this is only calculated at creation or during data refresh. This means it is fast at execution and it means that when new data arrives from a new month the measure automatically determines the new CurrentMonth.