topic Re: How to create a measure that will filter to last month and the month befores data? in DAX Commands and Tips

How to create a measure that will filter to last month and the month befores data?

alexw94 — Fri, 21 Apr 2023 15:00:01 GMT

I have measures allready created which shows percentage data, what I am wanting is 3 cards.

A card that shows this currents calendar months percentage data.

A card that shows the last calendar months percentage data.

A card that shows the previous calendar months percentage data.

Now the data I have does go over previous fiscal years. I am assuming the measure that will be created for the 3 above can simply be dragged and dropped on to the "Filters to visual" which will allow these to be done.

The objective here is these for these to automaticly calculate this month, last month and previous to last months data without the need for manual change.

Any help is greatly appreciated 🙂

Re: How to create a measure that will filter to last month and the month befores data?

johnt75 — Fri, 21 Apr 2023 15:17:27 GMT

I would create a calculation group with 3 calculation items, then put that calculation group on the card visuals and pick the appropriate item.

Current Calendar Month = CALCULATE ( SELECTEDMEASURE (), CALCULATETABLE ( DATESMTD ( 'Remote Date'[Date] ), TREATAS ( { TODAY () }, 'Remote Date'[Date] ) ) ) Last Calendar Month = VAR CurrentDate = TODAY () VAR StartLastMonth = EOMONTH ( CurrentDate, -2 ) + 1 VAR EndLastMonth = EOMONTH ( StartLastMonth, 0 ) RETURN CALCULATE ( SELECTEDMEASURE (), DATESBETWEEN ( 'Date'[Date], StartLastMonth, EndLastMonth ) ) Prev Calendar Month = VAR CurrentDate = TODAY () VAR StartPrevMonth = EOMONTH ( CurrentDate, -3 ) + 1 VAR EndPrevMonth = EOMONTH ( StartLastMonth, 0 ) RETURN CALCULATE ( SELECTEDMEASURE (), DATESBETWEEN ( 'Date'[Date], StartPrevMonth, EndPrevMonth ) )

Re: How to create a measure that will filter to last month and the month befores data?

yyzheng12 — Fri, 21 Apr 2023 15:29:13 GMT

Try the WINDOW() Dax. WINDOW function (DAX) - DAX | Microsoft Learn

Re: How to create a measure that will filter to last month and the month befores data?

alexw94 — Mon, 24 Apr 2023 07:35:48 GMT

johnt75 - I have tried using the above measures but they have not worked. I have added in the measures that are appropiate for my report at the DATESBETWEEN bit.

Also where it is SELECTRED MEASURE... am I suppose to be putting in the measure I am want calculating for the previous/current months data between those brackets?

Re: How to create a measure that will filter to last month and the month befores data?

johnt75 — Mon, 24 Apr 2023 09:22:29 GMT

They are not regular measures, they are calculation items. You need to use Tabular Editor to create a calculation group and then add the above code as calculation items. You can then add the calculation group to the filter for your visuals and choose the appropriate calculation item.

Re: How to create a measure that will filter to last month and the month befores data?

alexw94 — Mon, 24 Apr 2023 13:28:56 GMT

johnt75 I am restricted so therefore cannot use the Tabular Editor. If you or anyone knows of any alternate ways I can do this, then please let me know.

Re: How to create a measure that will filter to last month and the month befores data?

johnt75 — Mon, 24 Apr 2023 13:32:57 GMT

You'll could create separate measures for each. Just replace SELECTEDMEASURE() in my code with the actual measure you want to calculate.