I have a fairly large dataset of retailer sales info. I import last 1 week of sales each week and append to my Master "Retailer POS" table". However, the report from the retailer portal only includes items that showed sales data for the given week. So at times there is missing data for smaller or slower moving items that may not have sales data each week that come into my datatable.
I have a "CalendarMaster" and a separate "ItemMaster" table that links retailer POS data linked with a key field "UPC" in my item master. I have a table as a placeholder where all my calculations are stored called "Calculations".
All my report calculations for rolling periods (CY, YAG, L4, L4 YAG, etc.) work well at higher levels where these individual items or small brands don't break the visualizations due to gaps in sales because they are rolled into larger populations. But if I filter to smaller fields (Brand, item level, etc.) it doesn't work as well due to gaps in weekly sales.
Anyone have tips for creating a summarizing calculation that achieves similar time intelligence as DATEADD but is for a sum of a range of dates that doesn't require each week to be populated. I.e. Sum of week a, week b, week d etc. that still works if week C is missing? Or way to calculate sales a zero if it is missing a week of data?
Also happy to hear suggestions on how to create a new measure or calculation that I can use to filter the visual to automatically exclude the items that tend to keep my visual from working.
My RetailerPOS master is quite large (7MM lines) so posting the PBIX file is a bit challenging.
Dollars = SUM(RetailerPOS[DOLLAR SALES])
Dollars YAG = calculate('Calculations'[Dollars],DATEADD('CalendarMaster'[DateLong],-364,DAY))
Dollars vs YAG = 'Calculations'[Dollars] - 'Calculations'[Dollars YAG]
Dollars % vs YAG = 'Calculations'[Dollars vs YAG] / 'Calculations'[Dollars YAG]
Each week in my calendar master can be selected indivdually, as a slider, or as running times as separate measures
