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

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

Reply
learner03
Post Partisan
Post Partisan

Measure to get time/date

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.

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

@learner03 

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





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

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

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.

屏幕截图 2022-01-19 115041.jpg

屏幕截图 2022-01-19 113740.jpg

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

 

屏幕截图 2022-01-19 114638.jpg

the third table:

屏幕截图 2022-01-19 115315.jpg

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.

屏幕截图 2022-01-19 115720.jpg

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!

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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.

屏幕截图 2022-01-19 115041.jpg

屏幕截图 2022-01-19 113740.jpg

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

 

屏幕截图 2022-01-19 114638.jpg

the third table:

屏幕截图 2022-01-19 115315.jpg

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.

屏幕截图 2022-01-19 115720.jpg

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!

amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak 

I tried this but at the end, it does not show tha date which is beyond max calender date which is current date.

akapoor03_0-1642480393384.png

 

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"

@learner03 

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





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

Proud to be a Super User!




Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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