Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.
I have a column as Job Create time eg 14/01/2022 12:03:45 AM. I want to create a measure that shows the Job Close time as 48 hours plus to the Job Create time. But, if there is a weekend or public holiday in between, so it shoud skip that and only calculate based on working days. For Public holiday and weekend claculation I have a Calender table that has a column as ïf work day", where it shows 1 as working day and 0 as weekend and public holiday.
Solved! Go to Solution.
@learner03 , My blog on the same tpoic should help
Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...
Add 2 working days
pls try this
Column =
VAR next=minx(FILTER('Table (2)','Table'[create time]<'Table (2)'[DATE] && 'Table (2)'[IF WORKDAY]="Yes"),'Table (2)'[DATE])
return minx(FILTER('Table (2)',next<'Table (2)'[DATE] && 'Table (2)'[IF WORKDAY]="Yes"),'Table (2)'[DATE])+mod('Table'[create time],1)
pls see the attachment below
Proud to be a Super User!
Hi~ @learner03
According to your description,I created the file as follows:
We first need to create a table of public holidays,these data can be easily obtained from the Web source.I have created a simple table here, you can extend it as needed.
Then we need a calendar to filter out which dates are working days:
Table1 = CALENDAR(date(2022,01,01), date(2022,12,31))
Holiday = LOOKUPVALUE('PublicHolidayTable'[Name],PublicHolidayTable[Date],'Table1'[Date])
WeekDay = WEEKDAY('Table1'[Date],2)
Working Day =
SWITCH (
TRUE (),
ISBLANK ('Table1'[Holiday])
&& 'Table1'[WeekDay] < 6, "1",
ISBLANK ('Table1'[Holiday])
&& 'Table1'[WeekDay] >= 6, "0",
"0"
)
the third table:
Please create a measure:
JobEndTime =
VAR _time = TIMEVALUE(FORMAT(MAX(Table2[JobCreateTime]),"hh:mm:ss"))
VAR _table = TOPN(2,FILTER('Table1','Table1'[Date]>MAX(Table2[JobCreateTime])&&'Table1'[Working Day]="1"),'Table1'[Date],ASC)
VAR _newDate = MAXX(_table,[Date]) +_time
return
_newDate
And the result should look like this.
Hope this helps~
Best Regards,
Community Support Team _ Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hi~ @learner03
According to your description,I created the file as follows:
We first need to create a table of public holidays,these data can be easily obtained from the Web source.I have created a simple table here, you can extend it as needed.
Then we need a calendar to filter out which dates are working days:
Table1 = CALENDAR(date(2022,01,01), date(2022,12,31))
Holiday = LOOKUPVALUE('PublicHolidayTable'[Name],PublicHolidayTable[Date],'Table1'[Date])
WeekDay = WEEKDAY('Table1'[Date],2)
Working Day =
SWITCH (
TRUE (),
ISBLANK ('Table1'[Holiday])
&& 'Table1'[WeekDay] < 6, "1",
ISBLANK ('Table1'[Holiday])
&& 'Table1'[WeekDay] >= 6, "0",
"0"
)
the third table:
Please create a measure:
JobEndTime =
VAR _time = TIMEVALUE(FORMAT(MAX(Table2[JobCreateTime]),"hh:mm:ss"))
VAR _table = TOPN(2,FILTER('Table1','Table1'[Date]>MAX(Table2[JobCreateTime])&&'Table1'[Working Day]="1"),'Table1'[Date],ASC)
VAR _newDate = MAXX(_table,[Date]) +_time
return
_newDate
And the result should look like this.
Hope this helps~
Best Regards,
Community Support Team _ Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
@learner03 , My blog on the same tpoic should help
Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...
Add 2 working days
I tried this but at the end, it does not show tha date which is beyond max calender date which is current date.
I don't know how to extend another year in Calender.
My calender table's Advanced editor query is-
let
Source = List.Dates(StartDate, Length+1, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
StartDate = #date(2021, 7, 1),
Today = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),11)),
Length = Duration.Days(Today - StartDate),
Custom1 = #"Changed Type",
#"Inserted Year" = Table.AddColumn(Custom1, "Fin Year", each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Fin Month", each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6 , Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek(([Date]), Day.Sunday), Int64.Type),
#"Inserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MMM", each Text.Start([Month Name], 3), type text),
#"Inserted First Characters1" = Table.AddColumn(#"Inserted First Characters", "DDD", each Text.Start([Day Name], 3), type text),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters1",{"Date", "Fin Year", "Month Name", "MMM", "Fin Month", "Day Name", "DDD", "Day of Week"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "FYMM", each ([Fin Year]-2000)*100 + [Fin Month]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FYMM", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthID", Int64.Type}})
in
#"Changed Type2"
pls try this
Column =
VAR next=minx(FILTER('Table (2)','Table'[create time]<'Table (2)'[DATE] && 'Table (2)'[IF WORKDAY]="Yes"),'Table (2)'[DATE])
return minx(FILTER('Table (2)',next<'Table (2)'[DATE] && 'Table (2)'[IF WORKDAY]="Yes"),'Table (2)'[DATE])+mod('Table'[create time],1)
pls see the attachment below
Proud to be a Super User!
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
76 | |
74 | |
69 | |
48 | |
40 |
User | Count |
---|---|
61 | |
41 | |
33 | |
30 | |
29 |