Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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 😄
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |