Last 4 Week Sales, MTD, and LY using specific calendar

[Anonymous]

Hi, I have a specific calendar and have loaded it into my powerbi data source via excel. Weeks are calculated Mon-Sun and there are corresponding LY weeks.

For example, 3/15/2021 - 3/21/2021 is a week and it's corresponding LY is 3/16/2020-3/15/2020

I'm looking to create a dashboard where I always display the last 4 weeks of sales as of the current monday + the corresponding LY. I also need a month to date sale row which uses the current calendar month. 

It would look something like: 

Week Ending	Net Sales Amount	LY SALES
3/1/2021	100	100
3/8/2021	100	100
3/15/2021	100	100
3/22/2021	100	100
MTD	250	250

I've tried creating DAX measures where I subtract 7 days for the prior week, 14 for the -2 week, etc but am, unclear how to link a corresponding LY date. Also, I don't know how to keep the monday date static without manually changing the date filter. Any insight appreciated!

Sample Calendar Dates

FISCAL_MONTH	WE_DATE	DATE	WEEK_NO	LY_WE_DATE
MAR	2/21/2021	2/15/2021	27	2/23/2020
MAR	2/21/2021	2/16/2021	27	2/23/2020
MAR	2/21/2021	2/17/2021	27	2/23/2020
MAR	2/21/2021	2/18/2021	27	2/23/2020
MAR	2/21/2021	2/19/2021	27	2/23/2020
MAR	2/21/2021	2/20/2021	27	2/23/2020
MAR	2/21/2021	2/21/2021	27	2/23/2020
MAR	2/28/2021	2/22/2021	28	3/1/2020
MAR	2/28/2021	2/23/2021	28	3/1/2020
MAR	2/28/2021	2/24/2021	28	3/1/2020
MAR	2/28/2021	2/25/2021	28	3/1/2020
MAR	2/28/2021	2/26/2021	28	3/1/2020
MAR	2/28/2021	2/27/2021	28	3/1/2020
MAR	2/28/2021	2/28/2021	28	3/1/2020
MAR	3/7/2021	3/1/2021	29	3/8/2020
MAR	3/7/2021	3/2/2021	29	3/8/2020
MAR	3/7/2021	3/3/2021	29	3/8/2020
MAR	3/7/2021	3/4/2021	29	3/8/2020
MAR	3/7/2021	3/5/2021	29	3/8/2020
MAR	3/7/2021	3/6/2021	29	3/8/2020
MAR	3/7/2021	3/7/2021	29	3/8/2020
MAR	3/14/2021	3/8/2021	30	3/15/2020
MAR	3/14/2021	3/9/2021	30	3/15/2020
MAR	3/14/2021	3/10/2021	30	3/15/2020
MAR	3/14/2021	3/11/2021	30	3/15/2020
MAR	3/14/2021	3/12/2021	30	3/15/2020
MAR	3/14/2021	3/13/2021	30	3/15/2020
MAR	3/14/2021	3/14/2021	30	3/15/2020
MAR	3/21/2021	3/15/2021	31	3/22/2020
MAR	3/21/2021	3/16/2021	31	3/22/2020
MAR	3/21/2021	3/17/2021	31	3/22/2020
MAR	3/21/2021	3/18/2021	31	3/22/2020
MAR	3/21/2021	3/19/2021	31	3/22/2020
MAR	3/21/2021	3/20/2021	31	3/22/2020
MAR	3/21/2021	3/21/2021	31	3/22/2020

[MisterFry · Accepted solution]

I have a similar issue in the data that I use regularly. 

The solution in my case was to (in power query, or the source data) sort your weeks most recent first and create an index column that counts backwards from 0 (0 being the current week). I call the field 'week offset'.

This field solves both problems. Instead of doing crazy date gymnastics, you can show the last 4 weeks by showing weeks >= -4. Similarly, you can show the prior year by showing weeks -52 to -56, as the LY week is always 52 weeks prior. 

This solution also solved a problem I was having with our fiscal calendar that has a 53rd week every 5 years, and threw off all of our vs. LY reporting. (because the fiscal week that lines up correctly is always 52 weeks prior, even in years with 53 weeks) 
 

[MisterFry · Accepted solution]

I have a similar issue in the data that I use regularly. 

The solution in my case was to (in power query, or the source data) sort your weeks most recent first and create an index column that counts backwards from 0 (0 being the current week). I call the field 'week offset'.

This field solves both problems. Instead of doing crazy date gymnastics, you can show the last 4 weeks by showing weeks >= -4. Similarly, you can show the prior year by showing weeks -52 to -56, as the LY week is always 52 weeks prior. 

This solution also solved a problem I was having with our fiscal calendar that has a 53rd week every 5 years, and threw off all of our vs. LY reporting. (because the fiscal week that lines up correctly is always 52 weeks prior, even in years with 53 weeks) 
 

[Jimmy801]

Hello @Anonymous 

can not follow you. One time you are asking for the sales of the last 4 weeks (assuming using the current date) but then you are showing a table with rows. Should somehow used this date for showing the last 4 weeks sales? What has the specific calendar to do with your calculation? 4 weeks back is 4 weeks back, whatever calendar you are taking. However here an idea of a measure what you may need (not tested)

YourMeasure= var= ReferenceDate = Max(YourSalesTable[Date]) //or reference here the current date
Return
Calculate(sum(YourSalesTable[Sales], Filter(allselected(YourSalesTable), [Date]>=Dateadd(ReferenceDAte, -4, week)))

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi - thank you for your reply. I apologize for not being able to explain my question concisely. The table of rows I was showing was just to indicate the corresponding LY date with the Monday - Sunday weeks which may have been confusing.

I guess my root issue is that I always want to see 4 weeks' worth of sales M-Sun.

The report is run on Monday, so for example, my prior week sales would be 3/22/2021 - 3/28/2021

-2weeks would be 3/15-3/21

-3 weeks would be 3/8-3/14

-4 weeks would be 3/1-3/7

My MTD sales would be 3/1-3/28.

Now if I were to run this report today, I would still display the same dates with the exception that MTD is now 3/1-3/29.

If I run this report next week on Monday 4/5, my PW sales are now 3/29-4/4

-2 weekswould be 3/22 - 3/28

-3 week would be 3/15-3/21

-4 weeks would be 3/8-3/14

My MTD is now 4/1-4/4

If I were to use a generic date table, how can I write a generate dax measure that would aggregate my daily sales into these Monday - Sunday weeks as well as their corresponding LY dates?

Let me know if this makes more sense!

[Jimmy801 · Accepted solution]

Hello @Anonymous 

I think this is the kinda staff is best done in DAX. I didn't see your data but lets say you hava a fact table with your sales on daily basis. Then you create a dimension with dates where you add all information you need. In your case you need to have a field with year and week. You can also add information like starting date of this week and end date.

Then you connect both of this tables and use the year and week in your visuals to break down the measure of the sum of your sales and this should show you the desired result.

Feel free to contact me again

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy