Would love help getting Sales from Last 4 Weeks this year and Last 4 Weeks Year Ago

[Anonymous]

Hello There,

I have a Power BI Desktop Report that is updated from a sql database, where each retailer inputs their Point Of Sale $/Units results for the prior week at the beginning of the next week. So 7/23/22 Sales data is updated 7/25/22.

I would love to have a way to use a week/date slicer in order to select a range of dates and dynamically filter a visual to adjust for the Sales % Last 4 Weeks vs Last 4 Weeks Year Ago, Last 12 weeks vs Last 12 weeks year ago etc for that the time period selected in the date/time slicer.

Such as below....

This is my Excel Sample File of the Data table in my PBIX file. 

First tab is POS data

Second Tab is week table I created

https://www.dropbox.com/scl/fi/zachr4jzyrxo8fzqedy8v/Power-BI-Sample-Data.xlsx?dl=0&rlkey=cayyyia6ft...

This is my relationship with the week data

I would love to include my PBIX File, but most of the tables are direct sql queries in the file with proprietary company information. I can't find a good way to mask/remove that data. If anyone has an idea on how to do this, I am more than willing to try. 

[Ashish_Mathur]

Hi,

You will have to create a Calendar Table with a column in there for week numbers.  If you can do that, then we can solve this question with measures.

[Anonymous]

Hi Ashish,

Thank you for the tip! I have created a data table with weeks. Please see below for link. Second Tab is the week table.

https://www.dropbox.com/scl/fi/zachr4jzyrxo8fzqedy8v/Power-BI-Sample-Data.xlsx?dl=0&rlkey=cayyyia6ft...

Below is the relationship from my Sales Data to the Week Table

[Ashish_Mathur · Accepted solution]

Hi,

I have shown you how to calculate the units sold.  Do similar calculations for the other time dimensions as well.  Download the PBI file from here.

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur 

Thank you so much!!!This is very helpful!! The only Issue I am running into, is that when I use your logic to look at Last 4 Weeks YAGO, it goes back an extra week too much for some reason... wondering why that would be the case and how to solve?

[Ashish_Mathur]

You are welcome.  For calculating sales for 4 weeks ended the last date of the date selected in the slicer, replace the first max with min and 24 with 21.