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.
- jamuka8 months agoHelper IV
Dear Ashish_Mathur
Please find below my excel tables, I simply subtract related months sales quantity from related months forecast. If there's no sales for related month (because we are forecasting for upcoming two months therefore there will be no sales value for those months) then I do nothing.