Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I need help with the previous week's calculation.
I have the data consists of the start date and end date of the week and fiscal week column. I didn't have any date column apart from the start date and end date. I want to calculate the previous week which works in different granularity levels.
Please check the below screenshot.
I have tried with calander table week number also but it didn't worked and I have used relationship between two table is both side.
This the present calculation I have used.
Solved! Go to Solution.
@tulasi_pbi1988 , Check how I have use week rank to deal with week
Date = CALENDAR(Date(2018,01,01),TODAY())
Mark as Date Table
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 Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
And use week in Rank
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 12 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Next 3 period Sales =
Var _min = maxx(allselected('Date','Date'[period Rank])
Var _max = maxx(allselected('Date','Date'[period Rank]) +3
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))
Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.
@tulasi_pbi1988 , Check how I have use week rank to deal with week
Date = CALENDAR(Date(2018,01,01),TODAY())
Mark as Date Table
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 Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
And use week in Rank
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 12 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Next 3 period Sales =
Var _min = maxx(allselected('Date','Date'[period Rank])
Var _max = maxx(allselected('Date','Date'[period Rank]) +3
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))
Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.
Hi Amit,
Thank you for your response.
But, here in my data there is no Sales Date as you mentioned in your example.
I have only Start Date and End date. So I am not able to make a relationship with the calendar table using sales date and Date.
Hi @tulasi_pbi1988 ,
Would you please inform us more detailed information( your data(by OneDrive for Business)) if possible? Then we will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Dedmon Dai
Sorry, you screenshot doesn't make clear - which option is working and at what granularity are you missing?
See if this might help: https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison,
Sorry...
Measure Option1 is working.
For that I have created week Number in Fact table only.
Thank you
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |