Rolling 3 months DAX

1
votes

I have had a headache since yesterday with a measure.

My table is Data and in this table, I have a column Data[Date] which contains the first day of each month from January to June and a measure which calculates Total revenue.

I need a measure that calculates for June: Total REV = April + May + June.

I have tried this measure:

CALCULATE([TOTAL REV], DATESINPERIOD(Data[Date], LASTDATE(Data[Date]), -3, MONTH))

but in order to have the correct number I need to select all three months in the slicer, not just the month I am interested in.

2
powerbidax
Can you post your [Total revenue] measure? Your formula should work if it's something like SUM(Data[Revenue]). – Alexis Olson

2 Answers

0
votes

When you've got just one date selected in the slicer the filter context for the CALCULATE is just that month.

So I suspect that something similar to the below would fix your issue as it would allow the CALCULATE to consider the whole date table:

CALCULATE([TOTAL REV], DATESINPERIOD( ALL(Data[Date]), LASTDATE(Data[Date]), -3, MONTH))

0
votes

Try this:

sumx(DATESINPERIOD('Data'[Date], LASTDATE('Data'[Date]), -3, MONTH) , [TOTAL REV])