Forum Discussion
jamuka
8 months agoHelper IV
Difference between two table values with different dates
Dear all, I'm trying to calculate my forecast accuracy based on difference between sales and given forecast. we calculating forecast for current month and upcoming two months. Therefore when I'm...
- 8 months ago
Hi jamuka
Please try below measures:
Sales version 1 = var selected_calendar_month=MAX('Calendar'[StartofMonth]) var selected_actuals_month=CALCULATE(MAX(Sales[Date]),ALL('Calendar')) var selected_forecasting_month=CALCULATE(MAX('Forecast'[Forecasting Month]),ALL('Calendar')) var totalsalesactuals1=CALCULATE([Total Sales],ALL('Calendar'),FILTER(ALL(Sales),Sales[Date]=selected_forecasting_month)) RETURN totalsalesactuals1Forecast version 1 = var selected_calendar_month=MAX('Calendar'[StartofMonth]) var selected_forecasting_month=CALCULATE(MAX('Forecast'[Forecasting Month]),ALL('Calendar')) // var totalsalesforecast=SUMX(FILTER(ALL(Forecast),Forecast[Date]=selected_calendar_month && 'Forecast'[Forecasting Month]=selected_forecasting_month),Forecast[Total Forecast]) var totalsalesforecast1=CALCULATE([Total Forecast],ALL('Calendar'),FILTER(ALL(Forecast),Forecast[Date]=selected_calendar_month && 'Forecast'[Forecasting Month]=selected_forecasting_month)) RETURN totalsalesforecast1delta version 1 = IF(ISBLANK(Forecast[Forecast version 1]),BLANK(), [Sales version 1]-Forecast[Forecast version 1] )Below is the screesnhot:
Please give kudos or mark it as solution once confirmed.
Thanks and regards,
Praful
Ashish_Mathur
8 months agoSuper User
Hi,
In the expected result table, how did youa rive at 22 at the intersection of Jan'25 and Feb'25? It will be ideal if you can share an MS Excel file with formulas so that i can understand your calculation logic very clearly.