cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dhruvin Member
Member

How to find Week Start Date and Week End Date for Holidays ?

I have requirement like this. I have Calendar table in database. I have Public Holiday Column As well.

 

11/4/2018 = Sunday

11/5/2018 = Monday = National Holiday

11/6/2018 = Tuesday = National Holiday

11/7/2018 = Wednesday

11/8/2018 = Thursday

11/9/2018 = Friday

11/10/2018 = saturday

 

I need a column something like this: 

If day is "Monday" then it is my Week Start Date and day is "Friday" then it is my Week End Date. If there is holiday on Monday, then Tuesday would be my Week start date. If Tuesday is holiday my Week Start date would be Wednesday.

 

Same For Weekend as well, If there is holiday on Friday, I need Week end date as Thursday.

 

Any help on this, highly appriciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to find Week Start Date and Week End Date for Holidays ?

@Dhruvin Here is the DAX for this... (Created sample data and flagged few days as PublicHoliday)

Sample Data:

 

Test76Date = 
ADDCOLUMNS(
            CALENDAR("2018-10-01","2018-10-31")
           ,"Month",MONTH([Date])
           ,"WeekDay",WEEKDAY([Date],2)
           ,"WeekEnding",([Date]+5)-WEEKDAY([Date],2)
           ,"DayName", FORMAT ( [Date], "DDDD" )
           ,"PublicHoliday",IF( DAY ( [Date] ) IN {1,8,9,19,25,26},"Y","N")
)

Flag Logic:

 

Flag = 
VAR _WeekStart = CALCULATE(MIN([Date]),FILTER(Test76Date,Test76Date[PublicHoliday]="N" && NOT Test76Date[WeekDay] IN {6,7} && Test76Date[WeekEnding] = EARLIER(Test76Date[WeekEnding])))
VAR _WeekEnd = CALCULATE(MAX([Date]),FILTER(Test76Date,Test76Date[PublicHoliday]="N" && NOT Test76Date[WeekDay] IN {6,7} && Test76Date[WeekEnding] = EARLIER(Test76Date[WeekEnding])))
RETURN SWITCH(TRUE(),[Date]=_WeekStart,"WeekStart",[Date]=_WeekEnd,"WeekEnd",_WeekStart=_WeekEnd,"WeekStart/WeekEnd")

image.png

 

If there is only single working day in a week, then the script will show that day as "WeekStart/WeekEnd"



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





5 REPLIES 5
Super User
Super User

Re: How to find Week Start Date and Week End Date for Holidays ?

@Dhruvin Just want to be clear - You want a new column to display the text as "WeekStartDate" and "WeekEndDate" based on the rules defined above. So remaining days of the week it will be blank isn't it ? Is that you are looking for ? I understand that you have a PublicHoliday flag as well in the table.


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Dhruvin Member
Member

Re: How to find Week Start Date and Week End Date for Holidays ?

Yes, @PattemManohar You are right.

I need a column.

Super User
Super User

Re: How to find Week Start Date and Week End Date for Holidays ?

@Dhruvin Here is the DAX for this... (Created sample data and flagged few days as PublicHoliday)

Sample Data:

 

Test76Date = 
ADDCOLUMNS(
            CALENDAR("2018-10-01","2018-10-31")
           ,"Month",MONTH([Date])
           ,"WeekDay",WEEKDAY([Date],2)
           ,"WeekEnding",([Date]+5)-WEEKDAY([Date],2)
           ,"DayName", FORMAT ( [Date], "DDDD" )
           ,"PublicHoliday",IF( DAY ( [Date] ) IN {1,8,9,19,25,26},"Y","N")
)

Flag Logic:

 

Flag = 
VAR _WeekStart = CALCULATE(MIN([Date]),FILTER(Test76Date,Test76Date[PublicHoliday]="N" && NOT Test76Date[WeekDay] IN {6,7} && Test76Date[WeekEnding] = EARLIER(Test76Date[WeekEnding])))
VAR _WeekEnd = CALCULATE(MAX([Date]),FILTER(Test76Date,Test76Date[PublicHoliday]="N" && NOT Test76Date[WeekDay] IN {6,7} && Test76Date[WeekEnding] = EARLIER(Test76Date[WeekEnding])))
RETURN SWITCH(TRUE(),[Date]=_WeekStart,"WeekStart",[Date]=_WeekEnd,"WeekEnd",_WeekStart=_WeekEnd,"WeekStart/WeekEnd")

image.png

 

If there is only single working day in a week, then the script will show that day as "WeekStart/WeekEnd"



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Dhruvin Member
Member

Re: How to find Week Start Date and Week End Date for Holidays ?

Thanks
Dhruvin Member
Member

Re: How to find Week Start Date and Week End Date for Holidays ?

It would be great if you can share power bi file with the solution which you have prepared