Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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.

MakeItReal_0-1647815800433.png

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

DateStudent NameSubmissions
27/02/2022Lizui4
28/02/2022Laufenburg7
02/03/2022Tegalpapak8
05/03/2022Ar Rabiyah5
06/03/2022Gangarampur3
05/02/2022Bellegarde3
01/03/2022Luntas2
04/03/2022Seedorf2
04/03/2022Frei Paulo6
06/03/2022Bellegarde3
09/03/2022Gangarampur3
11/03/2022Cosamaloapan de Carpio7
13/03/2022Luntas2
15/03/2022Zagrodno9

 

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

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
v-henryk-mstf
Community Support
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


Looking forward to your feedback.


Best Regards,
Henry

ryan_mayu
Super User
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





Did I answer your question? Mark my post as a solution!

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?

 

DateISO Week numberStudent NameSubmissions
27/02/20209Lizui4
28/02/20209Laufenburg7
02/03/202010Tegalpapak8
05/03/202010Ar Rabiyah5
06/03/202010Gangarampur3
05/02/20215Bellegarde3
01/03/20219Luntas2
04/03/20219Seedorf2
04/03/20219Frei Paulo6
06/03/20219Bellegarde3
09/03/202110Gangarampur3
11/03/202110Cosamaloapan de Carpio7
13/03/202110Luntas2
15/03/202111Zagrodno9
27/02/20228Lizui4
28/02/20229Laufenburg7
02/03/20229Tegalpapak8
05/03/20229Ar Rabiyah5
06/03/20229Gangarampur3
05/02/20225Bellegarde3
01/03/20229Luntas2
04/03/20229Seedorf2
04/03/20229Frei Paulo6
06/03/20229Bellegarde3
09/03/202210Gangarampur3
11/03/202210Cosamaloapan de Carpio7
13/03/202210Luntas2
15/03/202211Zagrodno9

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a 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))))

 





Did I answer your question? Mark my post as a solution!

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:

MakeItReal_1-1647885445854.png

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

 

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.