Use sequential week number in calendar to find previous weeks data

[Anonymous]

Hello,

I have two tables in my Power BI model. A calendar table and a data table.

The calendar table has contiguous dates and sequential week numbers starting from 1 January 2020. The week number does not reset every year, so it just keeps increasing which is intentional since it can be used to always refer to the previous week or previous 2 weeks data. So, today is 20/03/2022 and the WeekSequenceNum is 116.

Below is my data table with the Date column in relationship with the Date column in the Calendar table, described above.

Date	Student Name	Submissions
27/02/2022	Lizui	4
28/02/2022	Laufenburg	7
02/03/2022	Tegalpapak	8
05/03/2022	Ar Rabiyah	5
06/03/2022	Gangarampur	3
05/02/2022	Bellegarde	3
01/03/2022	Luntas	2
04/03/2022	Seedorf	2
04/03/2022	Frei Paulo	6
06/03/2022	Bellegarde	3
09/03/2022	Gangarampur	3
11/03/2022	Cosamaloapan de Carpio	7
13/03/2022	Luntas	2
15/03/2022	Zagrodno	9

I am struggling to get this to work, but here is what i'm trying to do:

Use DAX measures to show the total submissions for previous week and previous 2 weeks, by using the WeekSequenceNum.

My approach would be to use today's date to find the corresponding WeekSequenceNum in the Calendar table and then minus 1 to get the previous WeekSequenceNum and then use that number to group all Submissions corresponding to that WeekSequenceNum. Then, do the same for the previous 2 weeks. But i don't know if this is possible? Any help is much appreciated!

[v-henryk-mstf]

Hi @Anonymous ,

Whether the advice given by @ryan_mayu @ has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out

Looking forward to your feedback.

Best Regards,
Henry

[ryan_mayu]

@Anonymous 

maybe you can try this

lasttwo week = CALCULATE([submission],FILTER(all('date'),'date'[week]=max('date'[week])-2))

pls see the attachment below

[Anonymous]

Hi @ryan_mayu Thanks for your reply! Your method works for the table in the first post but if there is more than one year of dates, there will be repeating week numbers. In this case, how to dynamically find the last week and week before last week total submissions? My idea is to correlate the Date in the table below with the Calendar table mentioned in the first post and then use the sequential week numbers. Is that possible?

Date	ISO Week number	Student Name	Submissions
27/02/2020	9	Lizui	4
28/02/2020	9	Laufenburg	7
02/03/2020	10	Tegalpapak	8
05/03/2020	10	Ar Rabiyah	5
06/03/2020	10	Gangarampur	3
05/02/2021	5	Bellegarde	3
01/03/2021	9	Luntas	2
04/03/2021	9	Seedorf	2
04/03/2021	9	Frei Paulo	6
06/03/2021	9	Bellegarde	3
09/03/2021	10	Gangarampur	3
11/03/2021	10	Cosamaloapan de Carpio	7
13/03/2021	10	Luntas	2
15/03/2021	11	Zagrodno	9
27/02/2022	8	Lizui	4
28/02/2022	9	Laufenburg	7
02/03/2022	9	Tegalpapak	8
05/03/2022	9	Ar Rabiyah	5
06/03/2022	9	Gangarampur	3
05/02/2022	5	Bellegarde	3
01/03/2022	9	Luntas	2
04/03/2022	9	Seedorf	2
04/03/2022	9	Frei Paulo	6
06/03/2022	9	Bellegarde	3
09/03/2022	10	Gangarampur	3
11/03/2022	10	Cosamaloapan de Carpio	7
13/03/2022	10	Luntas	2
15/03/2022	11	Zagrodno	9

[ryan_mayu]

@Anonymous 

You can try this

lasttwo week = 
IF(MAX('date'[week])=1,CALCULATE([submission],FILTER(ALL('date'),'date'[year]=MAX('date'[year])-1&&'date'[week]=53)),IF(MAX('date'[week])=2,CALCULATE([submission],FILTER(ALL('date'),'date'[year]=MAX('date'[year])-1&&'date'[week]=52)),CALCULATE([submission],FILTER(all('date'),'date'[year]=MAX('date'[year])&&'date'[week]=max('date'[week])-2))))

[ryan_mayu · Accepted solution]

@Anonymous 

You can try this

lasttwo week = 
IF(MAX('date'[week])=1,CALCULATE([submission],FILTER(ALL('date'),'date'[year]=MAX('date'[year])-1&&'date'[week]=53)),IF(MAX('date'[week])=2,CALCULATE([submission],FILTER(ALL('date'),'date'[year]=MAX('date'[year])-1&&'date'[week]=52)),CALCULATE([submission],FILTER(all('date'),'date'[year]=MAX('date'[year])&&'date'[week]=max('date'[week])-2))))

[Anonymous]

@ryan_mayu Thanks for your help! I tested v2 and made some minor modifications, by renaming the columns for clarity and set ISO week number in the 'date' table:

ISOweeknum = WEEKNUM('date'[Date],21)

But i noticed an error as below:

Also, i cannot understand what is "lasttwoweek" referring to?

The objectives: For example, if the current year is 2022 and current ISO week number is 12. Then, i want to find the total Submissions for previous week (week number 11) and previous 2 weeks (week number 10), in two separate columns.

Here is the formula that i copied from the measure:

lasttwo week = 
IF(MAX('date'[ISOweeknum])=1,CALCULATE([CalculatedSubmissions],FILTER(ALL('date'),'date'[year]=MAX('date'[year])-1&&'date'[ISOweeknum]=53)),IF(MAX('date'[ISOweeknum])=2,CALCULATE([CalculatedSubmissions],FILTER(ALL('date'),'date'[year]=MAX('date'[year])-1&&'date'[ISOweeknum]=52)),CALCULATE([CalculatedSubmissions],FILTER(all('date'),'date'[year]=MAX('date'[year])&&'date'[ISOweeknum]=max('date'[ISOweeknum])-2))))

[ryan_mayu]

@Anonymous 

last two week is to get previous 2 weeks value.

the second row week 8 gets the value of week 6 which is 8