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

[alexw94]

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 🙂

[yyzheng12]

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

[johnt75]

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 )
    )

[alexw94]

@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?

[johnt75]

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.

[alexw94]

@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. 

[johnt75]

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