topic Re: MAX Date filter in DAX Commands and Tips

MAX Date filter

Anonymous — Tue, 15 Sep 2020 07:42:53 GMT

Good Day,

I have an issue where a DAX formula is not ouputting correctly. Essentially I wrote formulas to calculate the value difference of items from a previous month, which ouputs correctly. Now I want to filter on the MAX selected date (wrote this in DAX) for those values but it does not work.

1. The matrix output on the far left is correct
2. The table output in the middle via a DAX filter does not work ([Latest Month], [Date Checker]) Code can be found in PowerBI file
3. The table output on the right (manual filter) works perfectly.

I'm not sure where the formula is breaking.I believe it is somewhere along the lines because of the relationship between the two tables in PowerBI.

Let me know if I need to furnish more details.Please find the links to the excel base data and Power BI File that may assist.

Excel - https://www.dropbox.com/s/ifqq493hvw781ub/Test.pbix?dl=0

PowerBI - https://www.dropbox.com/s/8cmm6vpbpxwh0bo/Test.xlsx?dl=0

Re: MAX Date filter

amitchandak — Tue, 15 Sep 2020 08:11:25 GMT

Anonymous , Not checked your file. But you can have measure like ?

measure =
var _max = maxx(allselected(Date), Date[Date])
return
calculate([Value measure], filter(Date,Date[Date]= _max))

Re: MAX Date filter

Anonymous — Tue, 15 Sep 2020 08:28:59 GMT

Hi amitchandak,

Slightly confused on the [Value measure] syntax - is that self-referencing ?

I tried to do it this way to get my latest month to run a filter but it does not work.

Latest Month =

VAR CurrentYearMonth =

MAXX(ALLSELECTED('tertiary vw_insights_ageing_profile'),'tertiary vw_insights_ageing_profile'[Date Link])

Return IF(max('tertiary vw_insights_ageing_profile'[Date Link]) = CurrentYearMonth,1,0)

Re: MAX Date filter

amitchandak — Tue, 15 Sep 2020 09:16:46 GMT

Anonymous , Try like

measure =

VAR CurrentYearMonth = MAXX(ALLSELECTED('tertiary vw_insights_ageing_profile'),'tertiary vw_insights_ageing_profile'[Date Link])
Return

IF(format(max('tertiary vw_insights_ageing_profile'[Date Link]),"YYYYMM") = format(CurrentYearMonth,"YYYYMM"),1,0)

You can use today() in place of MAXX(ALLSELECTED('tertiary vw_insights_ageing_profile'),'tertiary vw_insights_ageing_profile'[Date Link]) , if needed

Re: MAX Date filter

Anonymous — Tue, 15 Sep 2020 13:46:37 GMT

amitchandak

I've written it exactly as you typed and it is unfortuantely still not working.

Perhaps the attached PBI file might be of some use ?