How to design a filter so that the most recent month with transactions will be displayed

[Anonymous]

Say I got a sales table, how do I set a filter so that the displayed month = max (sales date)? EG if the most recent transaction is June 2016, set the filter to monthyear = June 2016.

[v-qiuyu-msft]

Hi @Anonymous,

In your scenario, you can create a measure to get the most recent date. Then create a custom column into the table to tell us which rows have most recent date. Then add this new column on Visual/ Page/ Report filter property. For more information, please refer to this blog: Finding the Latest Date in Power BI Desktop .

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Anonymous]

@v-qiuyu-msft Thanks this came in with a blog page which looked very promising as to resolving what I need because I can use the filter in the page or report level. However, I was stopped when trying to create the custom column. I got the error "function 'Calculate' is not allowed as part of calculated column DAX expressions on DirectQuery mode". I guess the solution only works under import mode?

[v-qiuyu-msft]

Hi @Anonymous,

There are a set of DAX functions that have been disabled by default in DirectQuery mode. This is done in order to avoid performance issues with these functions when working in DirectQuery mode, as these calculations can get very expensive to run in the server.

In your scenario, if you want to allow “unrestricted measures” in DirectQuery mode, you can enable this option in the Options dialog.

Reference:
Ability to create measures when using DirectQuery mode

Best Regards,
Qiuyun Yu

[Anonymous]

@v-qiuyu-msft Sorry. The option is already enabled.

[v-qiuyu-msft]

Hi @Anonymous,

Based on my test, the CALCULATE() function is not available in custom column DAX expression, which is the same as error stated "Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.". In your scenario, please use connection mode as Import mode instead of DirectQuery mode then follow the steps in the article to finish your job. And you can click Refresh button to get the latest data.

Best Regards,
Qiuyun Yu

[achinm45]

Hi @Anonymous

You can do following :

1) Create  measures which will return Month of last transaction i.e. MAXMONTH = Month(MAX(Date) and another for Year i.e. MAXYEAR= YEAR(MAX(DATE))

2) Now in your calculations you have to put filter condition for filtering example

FILTER(SalesTable,Month(SalesTable[Date])=MAXMONTH && Month(SalesTable[Date])=MAXYEAR  )