CODEWITHSUNDEEP
dynamicpowerbi
vbeu
vbeu

Reputation: 13

Power BI dynamic rolling 12 months measure

I am trying to find a way to display the rolling 12 month sum of the last 12 months. My table is called CV_Spend__R2:

enter image description here

Year and Month come from a date hierarchy from the same table while Profit is a calculated measure (SUM) of that particular implicit measure. R12 is the rolling sum of the last 12 months contained in the dataset:

R12 = CALCULATE(SUM(CV_SPEND_R2[Profit]), 
DATESBETWEEN(CV_SPEND_R2[EOM].[Date], 
    NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(CV_SPEND_R2[EOM].[Date]))), 
    LASTDATE(CV_SPEND_R2[EOM].[Date])))

So far so good. Now the measure TTM should show me the same value as R12 but only for the last 12 months from a month/year date that I select. For this I created a separate date table with no relationship with the model:

DATE = CALENDAR(MIN(CV_SPEND_R2[EOM].[Date]), MAX(CV_SPEND_R2[EOM].[Date]))

enter image description here

For example, I want the TTM to show only the R12 values for March 2019 to March 2020 if March 2020 is selected as in the end R12 needs to be removed from the table.

I somehow managed to make this dynamic, but it is not working correctly and I am at wits' end. This is what I came up with:

TTM = 
VAR CurrentDate = MAX('DATE'[Date])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-12, DAY(CurrentDate))
VAR Result1 = CALCULATE([R12], FILTER(CV_SPEND_R2,CV_SPEND_R2[EOM] >= PreviousDate && CV_SPEND_R2[EOM] <= CurrentDate))

The problem with this is that it displays the correct months dynamically, but then it breaks down the the R12 value into the twelve months it contains in the interval, basically rolling back my efforts. If I add the TTM value on a card it shows the correct value for that month. I have tried other options with DATESINPERIOD, DATESYTD and DATESBETWEEN, but they either aren't dynamic to show only the last 12 months based on my selection, or they don't show the desired values.

enter image description here

I have also tried:

    TTM = 
VAR CurrentDate = MAX('DATE'[Date])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-12, DAY(CurrentDate))
VAR Result5 = IF(MAX(CV_SPEND_R2[EOM].[Date])<=CurrentDate && MIN(CV_SPEND_R2[EOM].[Date])>= PreviousDate,[R12])

The problem with this is that when I try to display it in a stacked column chart with TTM and [EOM] on the axis, nothing is displayed because it does not accept TTM in the chart, and the chart is my ultimate goal.

Can you please assist me with a solution for this? How to display only 12 months of rolling 12 months' sum from a selection? Please mind that in this example the March 2019 value also needs to be the sum of its previous 12 months, because I already found a solution to start the rolling 12-months sum in the dynamic interval (i.e. March 2019 = 1.028 bn), but this is not what I need. I want to see the R12 value for the 12 months in the TTM column so that I can display it in a stacked column chart later.

Thanks a lot.

Upvotes: 0

Views: 5704

Answers (1)

msta42a
msta42a

Reputation: 3741

After a quick look at your measures, it looks like you only need to slightly modify one. Replace table name in FILTER to ALL(CV_SPEND_R2)

TTM = 
VAR CurrentDate = MAX('DATE'[Date])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-12, DAY(CurrentDate))
VAR Result1 = CALCULATE([R12], FILTER(ALL(CV_SPEND_R2),CV_SPEND_R2[EOM] >= PreviousDate && CV_SPEND_R2[EOM] <= CurrentDate))

Upvotes: 0

Related Questions