Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 

if(WEEKDAY(DIM_DATE[DATE_DIMENSION_DATE])=1,0,1), so it will falg out all the week end dates with the flag 0 and rest with 1.
 
Really need some hint/solution for it.
 
Regards,
Siddhesh
8 REPLIES 8
gdarakji
Resolver III
Resolver III

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.

Anonymous
Not applicable

It's not working. Like I am trying to find solution where it can show me only those months week end dates based on the selection of any dates/week end dates.. 

Anonymous
Not applicable

I am using matrix visual and accordingly that visual should show those week end dates as well based on the selection of any dates/week end dates

amitchandak
Super User
Super User

@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 format

 

 

measures 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))

Anonymous
Not applicable

It's only for the next four weeks.. Its not working only for the selected month week end dates.i mean it's going further in next months week end dates.. 

Anonymous
Not applicable

Great thanks for your prompt response.. But how it will determine whether the selected date is if current month and the running week, as per this it should show me the next weekend dates data only.. But sure I will try this one.. So it's basically for next four weeks only.. Not the weekend dates.. 

@Anonymous you can add a [Week End Date] column to your DIM_DATE table. The [Week End Date] will contain the week end date corresponding to this particular date. This way, no matter what date is selected, you can always read the [Week End Date] of that date.

Anonymous
Not applicable

Its already there but i need to find the next week end date based on the selection of any dates, lets say if select 18th May then its weekend date is 23, so basically the output should be 30th May 2021.. that is how it should work, if i choose 2nd May then it should show me the next week end dates such as 9th, 16th 23 and 30 May 2021. This is what i am trying to achive.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors