cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Use sequential week number in calendar to find previous weeks data

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!

1 ACCEPTED SOLUTION
Super User

@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))))``````

Proud to be a Super User!

7 REPLIES 7
Community Support

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

Best Regards,
Henry

Super User

@Anonymous

maybe you can try this

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

pls see the attachment below

Proud to be a Super User!

Anonymous
Not applicable

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

Super User

@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))))``````

Proud to be a Super User!

Super User

@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))))``````

Proud to be a Super User!

Anonymous
Not applicable

@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))))``````

Super User

@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

Proud to be a Super User!