Forum Discussion
tulasi_pbi1988
6 years agoHelper I
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 ...
- 6 years ago
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.
AllisonKennedy
6 years agoCommunity Champion
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/
- tulasi_pbi19886 years agoHelper I
Hi Allison,
Sorry...
Measure Option1 is working.
For that I have created week Number in Fact table only.
Thank you