Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
Proud to be a PBI Community Champion
Proud to be a PBI Community Champion
@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"
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |