Need Help Calculating SUM of measures by time with DATEADD when there is a gap in weekly data

[Anonymous]

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

[Icey]

Hi @Anonymous ,

Please try to create another table with all values of "2021 PD 01 WK 1 (01)", "2021 PD 01 WK 2 (02)", "2021 PD 01 WK 3 (04)", "2021 PD 01 WK 4 (04)" like this:

NewTable =
DISTINCT ( 'YourTable'[Weeks] )

And then, create relationship between YourTable and the NewTable based on the two week columns.

Finally, replace the week column in YourTable with the week column in the NewTable in the Matrix visual.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Unless I'm not understanding your comment "on the two week columns" I have a similar set up now with a table that contains all unique weeks with no duplicates (see attached relationship). My issue is that my data source doesn't generate a row in the export from their system for that individual item if there were no sales on a given week.  So my data source has plenty of holes in it for missing weeks by location, but I just need for my calculation to "overlook" a missing week by either adding in missing data, or replacing blanks, or using a range of unique week rank vs time intelligence formulas.  

 Relationships with a Unique Week Table

[Anonymous]