Compare this week last year data, USING week number but DISPLAYING current week end date or similar

[apmulhearn]

Hello, and many thanks, in advance, for any help possible.

 I've been tasked with replicating an Excel report that gets sent out every week

The way that it is currently distributed, we review data on a weekly basis, and compare to the same week last year.

The complication (at least, for me) is that what they are REALLY looking at is week numbers (week 1, week 2, etc) but they only want to see it with dates.

I've been able to create a week number formula in BI. 

(Mktg Wk No = WEEKNUM('Date Table'[Date],02))

I've been able to create a week end date in BI.

(Mktg Week End date = 'Date Table'[Date]- WEEKDAY('Date Table'[Date],2) +7)

What I want is to display on a row the weekend date, then show the performance for that same week number in prior years. 

The photo with the handwriting on it is how, ideally, it would look.

The photo below is the workaround I use to find those numbers...but I'm having a hard enough time getting people to try and use BI reports, so I'd like to give them what they want. A small export of the workaround table is also below.

The booked guest and bookings figures are joined on the date to a date table from a guest table, and then summarized by week end date.

Mktg Wk NoMktg Week End date# Booked Guests# BookingsYear

1	1/5/2020 0:00	6	3	2020
1	1/6/2019 0:00	43	14	2019
2	1/10/2021 0:00	14	6	2021
2	1/12/2020 0:00	58	31	2020
2	1/13/2019 0:00	67	21	2019
3	1/17/2021 0:00	25	14	2021
3	1/19/2020 0:00	63	28	2020
3	1/20/2019 0:00	43	20	2019
4	1/24/2021 0:00	41	13	2021
4	1/26/2020 0:00	56	44	2020
4	1/27/2019 0:00	53	23	2019
5	1/31/2021 0:00	17	7	2021
5	2/2/2020 0:00	56	31	2020
5	2/3/2019 0:00	51	26	2019
6	2/7/2021 0:00	47	16	2021
6	2/9/2020 0:00	40	17	2020
6	2/10/2019 0:00	35	18	2019
7	2/14/2021 0:00	20	10	2021
7	2/16/2020 0:00	46	29	2020
7	2/17/2019 0:00	32	12	2019
8	2/21/2021 0:00	16	12	2021
8	2/23/2020 0:00	43	30	2020
8	2/24/2019 0:00	53	18	2019
9	2/28/2021 0:00	29	15	2021
9	3/1/2020 0:00	41	25	2020
9	3/3/2019 0:00	51	26	2019
10	3/7/2021 0:00	31	12	2021
10	3/8/2020 0:00	43	15	2020
10	3/10/2019 0:00	30	13	2019
11	3/14/2021 0:00	28	5	2021
11	3/15/2020 0:00	22	11	2020
11	3/17/2019 0:00	40	17	2019
12	3/21/2021 0:00	31	10	2021
12	3/22/2020 0:00	5	5	2020
12	3/24/2019 0:00	38	17	2019

[v-alq-msft]

Hi, @apmulhearn 

Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a matrix visual like below.

Best Regards

Allan

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

[apmulhearn]

Hello Allan,

Thank you so much for taking the time to help me!  For ME, this would be a fine solution. For my end user, I think it will still be visually confusing for two reasons - but maybe those are fixable in a way I'm not thinking of?
1. The YEAR columns would need to sort descending

2.  The Mktg Week End Date would only desirtably display in the most recent column. 

I've displayed a crude redline below. Can you tell me if you know a clever way to do this?

Thank you again for your kind help,

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@apmulhearn , Create a date table

Have a new column

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

And create measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Also refer

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8