YTD Actual and Forecasts with Measures

[Anonymous]

I want to create a YTD Actual measure  and a YTD Forecast measure to compare.    So, using the data from the screenshot and attached PBIX, the YTD actual would = 186, and the Forecast YTD  = 200.

Can someone provide some advice on how I can do this? 

There are lots of  questions about  YTD Actuals vs YTD Forecast, on this forum, such as  this one here. These examples appear to be different from my senario. In the previous examples (I have seen) Actuals and Forecast values are in seperate columns, while in my situation, my Actuals, and Forecast are calculated measures.  

Notes

1) Screenshot below, and PBIX file available here.

2) BTW, I can't use NOW() in the calculations, as YTD results are not "realtime" i.e the most recent Actuals are from Oct 2017

3) The PBIX file has a DateDim Table  if that helps  for the solution

Cheers

Steve

[Ashish_Mathur · Accepted solution]

Hi,

Modify your measures to:

Forecast1(Measure) = SUMX(FILTER(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",[Actuals(Measure)],"EFGH",CALCULATE(SUM('Revenue'[Value]), 'Revenue'[ValueType]= "Forecast 1")),[ABCD]>0),[EFGH])

Forecast2(Measure) = SUMX(FILTER(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",[Actuals(Measure)],"EFGH",CALCULATE(SUM('Revenue'[Value]), 'Revenue'[ValueType]= "Forecast 2")),[ABCD]>0),[EFGH])

Hope this helps.

[afzalphatan]

Hi, 

Before I present my solution, I would recommend you to unpivot "Value type" in power query, that would make you easy to author simple dax..

Secondly, use DimDate table starts with Jan 1st and end with Dec 31st, so that you can use DAX date functions without any issues...  

Below are my DAX formula to meet ur requirement.... Not much complicated though. 

Actuals_MyMeasure = IF( CALCULATE(SUM(Revenue[Value]), Revenue[ValueType] = "Actuals") > 0,
CALCULATE(SUM(Revenue[Value]), FILTER(ALL(Revenue[Date]), Revenue[Date] <= MAX(Revenue[Date])), Revenue[ValueType] = "Actuals"))

Forecast1_MyMeasure = IF( CALCULATE(SUM(Revenue[Value]), Revenue[ValueType] = "Actuals") > 0,
CALCULATE(SUM(Revenue[Value]), FILTER(ALL(Revenue[Date]), Revenue[Date] <= MAX(Revenue[Date]) && CALCULATE(SUM(Revenue[Value]), Revenue[ValueType] = "Actuals") > 0) , Revenue[ValueType] = "Forecast 1"))

I should have used Var to make the formula more intuitive... 

Anyways .... Hope you enjoy..if this helps you more... i shall try with Var aswell 😄

[Ashish_Mathur · Accepted solution]

Hi,

Modify your measures to:

Forecast1(Measure) = SUMX(FILTER(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",[Actuals(Measure)],"EFGH",CALCULATE(SUM('Revenue'[Value]), 'Revenue'[ValueType]= "Forecast 1")),[ABCD]>0),[EFGH])

Forecast2(Measure) = SUMX(FILTER(SUMMARIZE(VALUES(DimDate[Date]),[Date],"ABCD",[Actuals(Measure)],"EFGH",CALCULATE(SUM('Revenue'[Value]), 'Revenue'[ValueType]= "Forecast 2")),[ABCD]>0),[EFGH])

Hope this helps.

[Anonymous]

Works perfectly. Thanks alot. Ashish can you provide a brief summary of the logic used?  I don't understand the significance of the dummy text values ("ABCD", "EFGH". Cheers Steve 

[Ashish_Mathur · Accepted solution]

Hi,

You are welcome.  The SUMMARIZE function creates a virtual Table with Dates in the first column and 2 additional columns with the title of ABCD and EFGH.  The ABCD column has the actual numbers (the measure that you had already written) and the EFGH column has the Forecast 1 values.  I am then filtering this virtual table on the ABCD column with a criteria of >0.  As the very last step, I am then adding the numbers in the EFGH column.

Hope this helps.