Forum Discussion
Previous 13 weeks report
Hi All,
I am new to Power BI and need some help with a DAX query. I have to get last 13 weeks data and do a compare between 13 weeks data prior to that. Ex: If I am in week 27 , need to pull weeks 26 to 14 and compare it with weeks 13 to 1 or 1 to 13.
I tried parallelperiod,previousqtr options but didnt get the correct results.
would really help if someone can advise on this.
Thanks.
AN_PBI , refer to my blog on how to handle the weeks using date calendar. This can be a Week Calendar, Rank is important
Last 13 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Last 13 before 13 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-26 && 'Date'[Week Rank]<=max('Date'[Week Rank]) -14 ))
You need have few columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) Week Number = WEEKNUM([Date],2) Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number]) Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
5 Replies
- amitchandak
Super User
AN_PBI , refer to my blog on how to handle the weeks using date calendar. This can be a Week Calendar, Rank is important
Last 13 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Last 13 before 13 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-26 && 'Date'[Week Rank]<=max('Date'[Week Rank]) -14 ))
You need have few columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) Week Number = WEEKNUM([Date],2) Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number]) Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Thanks Amit, I was able to get the expected results. I had the cldr table with all the details, but was not sure how to use it.
All the details you shared helped me. Thanks again for the great post.
v-yingjlCommunity Support
Hi AN_PBI ,
Assume you have a date table and it has a week column, not related with the fact table, you can create two simliar control measures like this and set its value as 1 in the visual filter:
13 weeks control = IF ( SELECTEDVALUE ( 'Date'[Week] ) - 13 <= SELECTEDVALUE ( 'Table'[week] ) && SELECTEDVALUE ( 'Date'[Week] ) - 1 >= SELECTEDVALUE ( 'Table'[week] ), 1, 0 ) previous 13 week control = IF ( SELECTEDVALUE ( 'Date'[Week] ) - 26 <= SELECTEDVALUE ( 'Table'[week] ) && SELECTEDVALUE（'Date'[Week]）-14 > = SELECTEDVALUE（“Table” [week]）， 1， 0 ）Sample file is attached that hopes to help you, please check and try it: Previous 13 weeks report.pbix
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
