I've been working on this for about 5 days now,... and really stuck... Could use help... I've attached my Power BI table..
Trying to subtract "ForecastCore3" from CM_Ship Actuals...
Need to use Current Month for "CM_Ship_Actuals" and current month - 3 for "ForecastCore3"
Is this possible in DAX?
Thank you! I've tried what you suggested however still now lining correctly.... I had created a calendar table and no success.. Below is the output from my adjustments and I have also include more detailed..
Please try to create a calcuated column with following formula.
MAPE = VAR ValueThreeMonthAgo = LOOKUPVALUE ( Table2[ForcastCore3], Table2[RecordDate], EDATE ( Table2[RecordDate], -3 ), Table2[Businessline], Table2[Businessline] ) RETURN IF ( ISBLANK ( ValueThreeMonthAgo ), BLANK (), ABS ( Table2[CM_Orders_Actual] - ValueThreeMonthAgo ) / Table2[CM_Orders_Actual] )
Still having issues... I'm sure is something simple but just cannot figure it out... It does not like ProductLine or RecordDate...
It seems that you created a measure with my formula. Please try to create a calculated column instead.
What are you putting on the rows? Just the Date like shown? If so how are you narrowing down the values to a particular product? Slicing by product number?
The measure should be quite straightforward:
MAPE = VAR CM_Ship_Actuals = SUM ( Table[CM_Ship_Actuals] ) VAR ForecastCore3MoAgo = CALCULATE ( SUM ( Table[ForecastCore3] ), PARALLELPERIOD ( Calendar[Date], -3, MONTH ) ) RETURN DIVIDE ( CM_Ship_Actuals - ForecastCore3MoAgo, CM_Ship_Actuals )
But it depends on what you have in row/column/slicers or other filters...
Parallel Period should be your friend here:
You need contigeous dates for this to work.. if you dont have running dates use a calendar table 🙂
Forecast Current = SUM(Table1[Forecast])
Forecast -3 = CALCULATE([Forecast Current],PARALLELPERIOD(Table1[Date],-3,MONTH))
Get a sneak peek into this year's Power Platform Conference Keynote.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!