Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Forecast Accuracy with variable reference periods and sales and forecasts in separate tables

Hi there,

 

Here's a challenge I've been breaking my head over for quite some time now. the issue is quite simple in itself and tabled in different formats on this forum and others, yet slightly different from what I'm after. 

 

I've got 5 tables with data, 

sales                         contains sales on item code, country month aggregation (for a period of jan 2018 up to yesterday)

forecast archive       each 1st of the month, a snapshot it taken of the forecast, starting this new month and for a 24 month horizon. aggregation on item code, country, month

calendar                  self created table to reference periods

item master details base table containing all relevant product (item) codes. 

countries                 to enable a link between the sales table and the forecast archive table. 

 

I need to be able to compare the sales in any given month with the forecast for that month, where I can select the period in the archive based on which that forecast was stored. For example, for month 10 (October) I have the sales, and I'll have a forecast for this month from each of the snapshots from the forecast archive. So, on the first of July a snapshot of 24 months' forecast was taken, among which that of October, the same on the first of August, September and October. In theory, the closer one gets to the actual month of sales, the more accurate the forecast would be. To show this, I want to be able to trend the comparison between the sales and the forecasts from these different 'archiving months'. 

the analysis needs to bases on the item codes from the item master as it may be that for any given 'country/item' combination, there could be sales, but no forecast or forecast, but no sales. 

 

scope: 

item details:  ~ 1500 different items

sales: 18 countries, max 1500 codes, max 24 months; currently ~  80K records

forecast archive: 18 countries, max 1500 codes, each archive month, 24 months worth of forecast. current ~ 1M records

 

simple question is, how do I do this in power bi? 

 

Any suggested solution is well appreciated, in whichever format. as long as it works... 

 

thanks

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may try LOOKUPVALUE to add a measure.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.