How Do I keep Sales PY Calculation When a Filter is Applied?

[ESemba22]

Hi! I am an extreme novice and have scratched together some Dashboards in PBI with little to no knowledge of whether or not the way I am going abnout things is best practice.

My issue is I have time series data formatted below that goes back 104 weeks by item

I have a date table to roll up all of the various data sources I have so they have a consistent week ending date. (Although not neccesary for this dashboard as it only has one data source/retailer)

And then I created a Date Filter table so I could use slicers to select 52/26/12 weeks time periods

Using these calculations

Since there is no column for Sales PY in my data I just want to make a command that finds the sales for the same item/brand etc for the same exact period/week last year and this is what I came up with after failing with SAMEPERIODLASTYEAR

It appears to work when I don't have the date filter/slicer applied as shown below

However there are a few issues...
1.  Since my date table runs into the future it forward calculates the PY sales.  I only want my data to go up to the most recent current year date
2. When I apply my date filter slicer to 52 weeks the PY sales dissappear as shown below. I fundamentaly can understand why they would as the filter is only showing data from the last 52 weeks and therefore I would assume it omits going back further to pull those PY dates.  I just don't know how to go about fixing this

Let me know if any other context is necessary. Thank you!

[lbendlin]

Read about 

- unpivoting

- star and snowflake schema

- facts and dimensions

- difference between measures and calculated columns

Then refactor your data model, make sure you have the appropriate column types specified.  Take it light  on the measures.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ESemba22]

I have save the data file and Week Ending Rollup here

https://we.tl/t-JVjesRMY98

I ulimately want to be able to show latest 52w/26/w/13w/4w $ sales and $ sales in prior year for that time period

[lbendlin]

The weeks don't line up between your calendar table and your fact table.  For example on the calendar the week ending is 1/2/2022 whereas in the fact table it is 1/1/2022.

[ESemba22]

This is because if/when I want to add another retailer fact table the week ending dates are inconsistant.  Some retailers reporting ends 1/2... others 1/1... so I want to use the Week Ending Rollup column to artificaialy line all of the weeks up into one week ending date.

[lbendlin]

Would be better if you can get your source data in order. Trying to fix that in Power Query or DAX will be painful.

[ESemba22]

Do I even need the date roll up table? I guess I would like to solve this as easily and quickly as possible for the current data table. If I've added too many steps I'm simply just looking to get the time periods 52 26 13 and 4 $ sales and $ sales PY.  

[lbendlin]

There's nothing simple or easy about your scenario. Do it properly or risk wasting lots of time and energy on "quick" fixes.

[ESemba22]

So what do I need to get my source data in order?  As stated in the original post I am a complete novice.  What is the right question I need to be asking to get headed down the right path?