Forum Discussion
Measure to get time/date
- 4 years ago
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-Calendar-4-5-Power/ba-p/1187766Add 2 working days
- 4 years ago
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
- Anonymous4 years ago
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 _newDateAnd 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!