Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@Anonymous ,
You may try LOOKUPVALUE to add a measure.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |