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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

v-cgao-msft
Community Support
Community Support

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
v-cgao-msft
Community Support
Community Support

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

@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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.