Cumulative values for different periods and conditions

[Anonymous]

Hi there,

The main purpose of a dashboard I'm working on is to compare forecast values with actual values at any given time. Ideally, I should be able to compare the cumulative values for the last 12 months prior to a specified date (these values will be treated as forecast) with the cumulative values for the next 12 m after the specified date (these values will be considered as actual) by selecting Analysis Date in Slicer or on Diagram.

Then I need to calculate the percentage of variation between forecast and actual values in each quarter.

To put it schematically:

M1(-12m) M2(-9m) M3(-6m) M4(-3m) Analysis date M5(0m) M6(+3m) M7(+6m) M8(+9m) M9(+12m) , where Actuals and Forecast

I get updated files every quarter with the same columns but different values. To create the dashboard I’ve merged the last 5 files into one and it includes the following info (very simplified):

Since I don’t know how to create my “ideal” interactive dashboard, my plan is to create 12 measures based on given data. Something like this (these are not exact measures):

• Measure1 “12m Forecast” = IF ((File Update Date)=”sep.2019”, cumulative from sep.19 to sep.20, 0)
• Measure2 “9m FR” = IF ((File Update Date)=”dec.2019”, cuml from dec.19 to sep.20, 0)
• Measure3 “6m FR” = IF ((File Update Date)=”mar.2020”, cuml from mar.20 to sep.20, 0)
• Measure4 “3m FR” = IF ((File Update Date)=”jun.2020”, cuml from jun.20 to sep.20,0)

As the file, updated in sep.20, includes actual values from the previous periods, the measures for actuals are as follows:

• Measure5 “12m Actual ” = IF ((File Update Date)=”sep.2020”, cuml from sep.19 to sep.20, 0)
• Measure6 “9m AC” = IF ((File Update Date)=”sep.2020”, cuml from dec.19 to sep.20, 0)
• Measure7 “6m AC” = IF ((File Update Date)=”sep.2020”, cuml from mar.20 to sep.20, 0)
• Measure8 “3m AC” = IF ((File Update Date)=”sep.2020”, cuml from jun.20 to sep.20, 0)

And 4 measures to calculate % variance:

• Measure9 Var % 12m = (Measure1 “12m FR”- Measure5 “12m AC ”)/ Measure1 “12m FR”
• Measure10 Var % 9m = (Measure2 “9m FRt” - Measure6 “9m AC”)/ Measure2 “9m FR”
• Measure11 Var % 6m = (Measure3 “6m FR” - Measure7 “6m AC”) / Measure3 “6m FR”
• Measure11 Var % 3m = (Measure4 “3m FR” - Measure8 “3m AC”) / Measure4 “3m FR”

What I’ve already done:

1. I’ve unpivoted a combined table and got the following columns:

File Update Date

Project

WBS

Dates

Values

2. I tried to create a measure to calculate the cumulative / running total for a certain period and taking into account the “File update date”, but all that I got is the sum of the values for each month. 

Please share your ideas and thoughts on how to get the desired result, or at least something close to it.

Thanks.

[amitchandak]

@Anonymous , Not very clear. You can use measures like these with date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0),-13,MONTH)) //or use -12
Rolling 9 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX(Sales[Sales Date]),0),-10 ,MONTH)) //or use -9
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX(Sales[Sales Date]),0),-7 ,MONTH)) //or use -6
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX(Sales[Sales Date]),0),-4 ,MONTH)) //or use -3

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[littlemojopuppy]

Going to need some sample data to work with on this...