cancel
Showing results 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

Post Partisan

## WTD, MTD, FY YTD based on driver's work day

I have forklift driver's data that shows when the employee has scanned any item in the warehouse. That scan comes up as a timestamp. I have to calculate scan/hour in Week to date , MTH, FY YTD measure, where if there is NO timestamp for the driver, then that day should be treated as "non-working day" and thus excuded. Also these measure excludes weekend and public holidays.

I have been able to do this based on excluding public holiday and weekends but i a not getting how to work with the criteria of not cunting the hours of the employee's day where there is no timestamp.

example of Week to date is ( here, 7.5 is number of shift hours and shift starts at 6:00 am) ---

WTD =
VAR CurrentDate = LASTDATE('Calendar'[Date])
VAR DayNumberofWeek = WEEKDAY(LASTDATE('Calendar'[Date]),2)

VAR Transactions =
CALCULATE(
COUNT('ULDs - History'[TransactionType]),

VAR holidays = CALCULATE(COUNTROWS('Calendar'), 'Calendar'[If work day] = 1,

VAR TotalHours = (DayNumberofWeek-1) * 7.5 +
(((HOUR(NOW()) - 6)*60 + MINUTE(NOW()))/60) /*hours of today untill now */

RETURN
DIVIDE(
Transactions,
TotalHours,
0
)

I have converted timestamp as date format and mapped that to Calander table.

2 REPLIES 2
Super User

@learner03 , Based on what I got so far.

Assume you already sorted what work day and you can have column work day (value 1 and 0) in you date table

Have these columns

Week Start = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 // Monday week start

Month Start =  eomonth([DAte],-1)+1

Year Start = startofyear([Date]) // or date(year([date]),1,1)

Work week day = sumx(Filter(Date, Date[Week Start ] = earlier([Week Start]) && [Date] <= earlier([Date])), [work day])
Work Month day = sumx(Filter(Date, Date[Month Start ] = earlier([Month Start]) && [Date] <= earlier([Date])), [work day])
Work Year day = sumx(Filter(Date, Date[Year Start ] = earlier([Year Start]) && [Date] <= earlier([Date])), [work day])

Month Rank = RANKX(all('Date'),'Date'[Month Start],,ASC,Dense)

week Rank = RANKX(all('Date'),'Date'[Week Start],,ASC,Dense)

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Work Year day] <= Max('Date'[Work Year day]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Work Year day] <= Max('Date'[Work Year day])))

WTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[week Rank]=max('Date'[week Rank]) && 'Date'[Work week day ] <= Max('Date'[Work week day ]) ))
LWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[week Rank]=max('Date'[week Rank])-1 && 'Date'[Work week day ] <= Max('Date'[Work week day ])))

MTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && 'Date'[Work Month day ] <= Max('Date'[Work Month day ]) ))
LMTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && 'Date'[Work Month day ] <= Max('Date'[Work Month day ])))

refer if needed

raveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Work Year day] <= Max('Date'[Work Year day]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Work Year day] <= Max('Date'[Work Year day])))

Post Partisan

At the moment the ïf work Day" shows 1 on weekdays and 0 on weekends and public holidays.

I am stuck at how to make measure that does-- where there is no timestamp for a person , then assume that the person was not working that day.

example- for WTD if there was no timestamp for driver A on Monday and we are looking at the report on Thursday, then it should only take Tuesday, wednessday and thursday into consideration.

Similar situation with MTD and FY YTD.

Announcements

#### 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.