Forum Discussion
Consecutive Week End Dates
Hello Team,
Trying to implement the solution for the Consecutive Week End date, Below is the detail description of the same,
Let's say if i choose any date from my date slicer then it should consider the week end date for that date and then do the calculation for the next week end dates by excluding the week end date which it choosed earlier.
For E.g. If i choose 3rd May 2021 then its week end date is 2nd May 2021, so it should display the week end date such as 9, 16, 23 & 30th May 2021, Similalry if i choose 9th May 2021 then it should include only 16, 23, and 30 th May 2021, Similary if i choose, 16 then it should include 23, 30th May 2021 like wise calcualtion should happen.
Already calculated the Week end dates with the formula called
8 Replies
Anonymous , Create a date tbale with these new columns
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week end date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatmeasures for next 4 weeks
next 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])+1 && 'Date'[Week Rank]<=max('Date'[Week Rank]) +5))
You can use a measure similar to the below:
ConsecutiveWeekEndDate = FIRSTDATE(TOPN(1,SELECTCOLUMNS(FILTER(DIM_DATE,DIM_DATE[Week End Date]>=MAX(DIM_DATE[Week End Date])),"Next Week End Date",DIM_DATE[Week End Date]),[Week End Date],DESC))
The above measure needs some editing for table/column names as I don't have the model. If this doesn't work, you can share a pbix file with the model and sample data so we can further assist.
