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

[Dhruvin]

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.

[PattemManohar]

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

[Dhruvin]

Yes, @PattemManohar You are right.

I need a column.

[PattemManohar · Accepted solution]

@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")

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

[Dhruvin]

Thanks

[Dhruvin]

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