Forum Discussion
Calculating Previous without Contiguous date column but having start date and end date of week
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.
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.
5 Replies
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/
Hi Allison,
Sorry...
Measure Option1 is working.
For that I have created week Number in Fact table only.
Thank you
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
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
