Forum Discussion
Forecast Accuracy Measures based on lead time
I forecast demand for products that my company manufactures and sells. Our products have a 4 month lead time in manufacturing. I update the forecasts every month and the forecast goes out 12-18 months into the future. We call each month's forecast update a snapshot. I need help to develop measures to calculate forecast accuracy in comparing our actual shipments back to the snapshots. Becuase of our 4-month lead time, it complicates how we measure accuracy. As an example, in my January forecast snapshot, I have a forecast for January through December by month. For the accuracy measure, I need to sum up the total of the forecast of January through April from the January snapshot and drop that into the forecast month of January. I then need to compare this total back to what our actual shipments were for January through April. For the February snapshot, I need to sum up the forecast of February through May and then compare the actual shipments for February through May. I've included a screenshot of what my truncated data looks like and what I need the output to look like. I've color coded the data table to match up to the output data fields.
For the logic in the measure, I think it should be "if snapshot date equals forecast date, sum the forecast for the next 4 future forecast dates and apply this sum to the snapshot date". But how to do this in DAX? I would appreciate any help!
11 Replies
- Ashish_Mathur
Super User
Hi,
Share your forecase and actual data (share the .pbix file). Also, please share the expected result (actual numbers) for the data you share.
v-jiascu-msftMicrosoft Employee
Hi jasontlambert,
1. The Data table mixes up forecast data and actual data. That's hard for us to handle it. So split forecast and actual data.
2. Create a date table.
Calendar = CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) )
3. Establish relationships.
4. Create two measures.
ActualSum = CALCULATE ( SUM ( Actual[Value] ), DATESINPERIOD ( 'Calendar'[Date], FIRSTDATE ( 'Calendar'[Date] ), 4, MONTH ) )ForcastSum = CALCULATE ( SUM ( Forcast[Forecast] ), FILTER ( Forcast, Forcast[ForcastMonth] >= Forcast[SnapshotMonth] && Forcast[ForcastMonth] < DATEADD ( 'Calendar'[Date], 4, MONTH ) ) )5. Create visuals. Please check this file for details: https://1drv.ms/u/s!ArTqPk2pu-BkgSEe4BkpR_5JYwGA
Best Regards!
Dale
Thank you v-jiascu-msft! This is brilliant! I've been able to replicate this and it's working great. I knew it would involve datesbetween or datesinperiod, I just simply haven't fully grasped the DAX on those yet. This helps me a lot, and I'll be able to apply this in a lot of other places!
I'm noticing one minor issue, and it also exists in your example as well. The subtotal for the ActualSum in the table is giving me the same total as the first month. In the example, the January actual is 433 and the subtotal in the table visual is also 433. Do you know how to fix this?
- v-jiascu-msft
Hi jasontlambert,
Try this formula please.
ActualSum 2 = IF ( HASONEFILTER ( 'Calendar'[Date].[Month] ), CALCULATE ( SUM ( Actual[Value] ), DATESINPERIOD ( 'Calendar'[Date], FIRSTDATE ( 'Calendar'[Date] ), 4, MONTH ) ), SUM ( Actual[Value] ) )Best Regards!
Dale
- v-jiascu-msft
Hi jasontlambert,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale - v-jiascu-msft
Hi jasontlambert,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
DaleThanks for your help with this Dale. Sorry for not responding sooner. I've been on vacation from work. My first attempt at using your updated solution did not work in my full, large forecasting database. However, I did get it to work correctly in the sample data we've been working with. I'm unsure of what my next steps should be. I was hoping to tweak your formulas to get mine to work and then report back with what I had to do, but I've been unable to do so yet.
Hi v-jiascu-msft,
I've had some more time to work on this. I hope my findings can help us get a solution figured out.
The 4mth Forecast formula is working perfectly.
The 4mth Ships formula is working OK for each snapshot, but the subtotals still don't add up correctly when I try using your formula in my larger data set. Here's the DAX I'm using:
4mth Ships2 = IF (HASONEFILTER( 'Calendar'[Date].[Month] ), CALCULATE ( SUM ( 'Shipments Total'[Ships] ), DATESINPERIOD ( 'Calendar'[Date], FIRSTDATE ( 'Calendar'[Date] ), 4, MONTH ) ), SUMX ( SUMMARIZE ( 'Shipments Total', 'Calendar'[Date].[Month], "SumOfFourMonth", CALCULATE( SUM ( 'Shipments Total'[Ships] ), DATESINPERIOD ( 'Calendar'[Date], FIRSTDATE ( 'Calendar'[Date] ), 4, MONTH ) ) ), [SumOfFourMonth] ) )In the screenshot below, you can verify that the December snapshot yields 114 for the 4mth Fcst and 138 for the 4mth Ships. I am getting that result in the lower left table. I have snapshots now for December 2016 through October of 2017. The subtotal sum of the 4mth Forecast measure is 2,022 and is correct. The subtotal sum for 4mth Ships2 shows 2,234 but if you sum these numbers manually the total should be 2,408. So this is my first issue.
My second issue is that the 4mth Ships2 measure is throwing the December ships into September and beginning the 4mth summing process. I need the visual to begin in December with the 138 and stop at June, because June contains June-September shipments. I don't have October shipments yet, so I don't want to show the July snapshot yet.
When I try to filter the visual down to only show the months that I want, the subtotals are still an issue and seem to get worse.
Please let me know if I need to clarify anything further. The reason that the subtotal is so important is that I need to calculate our total error as a percentage, but I also need to see each month's snapshot error individually to see if errors are improving over time. Thank you again for your help with this!
