DAX: measure to calculate total available time in a selected period

[maclura]

Hi,

I am working on a report on technician utilisation.

I created several measures to perform time evaluations, which can be sliced by Date, Technician, Country, Activity, etc...

but there is a probem I don't know how to solve.

I try to explain

I have a time entries table like

TimeEntry.Id	Tech.Id	Date Start	Activity	Duration
1	a	2022-01-01	Holiday	8
2	b	2022-01-01	Holiday	8
3	c	2022-01-01	Office	9
4	d	2022-01-01	Office	9
5	a	2022-01-02	Travel	1
6	a	2022-01-02	Office	6
7	a	2022-01-02	Travel	1
8	b	2022-01-02	Travel	1.5
9	b	2022-01-02	Office	6
10	b	2022-01-02	Travel	1.5

I have a related technician table like

Tech.Id	Country	Standard Daily Working Time
a	Iceland	8
b	Slovakia	9
c	Singapore	8
d	Puerto Rico	9

I have a calendar table like

Date	WeekDayNumber
2022-01-01	5
2022-01-02	6
2022-01-03	0
2022-01-04	1
2022-01-05	2
2022-01-06	3
2022-01-07	4

I have a holiday calendar table like

Holiday Country	Holiday Name	Holiday Date
Thailand	New Year's Day	2022-01-01
France	New Year's Day	2022-01-01
Mexico	Constitution day	2022-02-07

Now, I need to create a measure to calculate what is the technician's standard available working time in the selected period with all the other filters applied. 

The first challenge for me is to keep in count that Saturday and Sunday have "Standard Daily Working Time" = 0 as well Holiday days in the technician's country.

And the second challenge is that I need the sum of the standard available working time of one or all technicians or any subset of them.

For example, if the technician slicers in the report have 2 technicians selected (one based in Brazil and the other in Japan) and my time slicer have Q1 2021 selected, I would like to calculate what is the sum of the standard available time of the 2 technicians put together for Q1 2021. aka MyMeasure = (tech 1 std time + tech 2 std time) in Q1 2021.

Having this measure would allow me to perform many more evaluation on utilisation.

Thanks for any hint.

maclura

[PaulOlding]

Hi @maclura 

Here's a measure to get total available working time.  It iterates the Technician table so will give you the result per technician and also in total.  There's an assumption that holidays in your Holiday Calendar are not on weekends.

Available Working Time = 
SUMX(
    Technician,
    VAR _Days = 
    CALCULATE(
        COUNTROWS('Calendar'),
        KEEPFILTERS('Calendar'[WeekDayNumber] < 5)
    )
    VAR _Country = Technician[Country]
    VAR _Holidays = 
    CALCULATE(
        COUNTROWS('Holiday Calendar'),
        TREATAS(VALUES('Calendar'[Date]), 'Holiday Calendar'[Holiday Date]),
        'Holiday Calendar'[Holiday Country] = _Country
    )
    VAR _Result = (_Days - _Holidays) * Technician[Standard Daily Working Time]
    RETURN
        _Result
)

[maclura]

Thanks @PaulOlding for your suggestion, but it doesn't work and it's my fault, because I didn't provide enough context.
We can't calculate the total available working time iterating the Technician table, because in this way my slicers on the Time entries table have no effect.

In particular, I have a filter to investigate what happens in working days compared to not-working days.
In fact, the Time entries table, with more context, looks like:

TimeEntry.Id	Tech.Id	Date Start	Activity	Duration	Is Not Working	Standard Daily Time
1	a	2022-01-01	Holiday	8	TRUE	0
2	b	2022-01-01	Holiday	8	TRUE	0
3	c	2022-01-01	Office	9	FALSE	8
4	d	2022-01-01	Office	9	FALSE	9
5	a	2022-01-02	Travel	1	FALSE	8
6	a	2022-01-02	Office	6	FALSE	8
7	a	2022-01-02	Travel	1	FALSE	8
8	b	2022-01-02	Travel	1.5	FALSE	9
9	b	2022-01-02	Office	6	FALSE	9
10	b	2022-01-02	Travel	1.5	FALSE	9

Now, the best I managed to calculate total available working time is whith this measure:

Time_Standard = 
SUMX(
    SUMMARIZE('Time Entry', 
        [Tech.Id],
        [Date Start],
        [Standard Daily Time]
    ),
    [Standard Daily Time]
)

But the issue is that, when there are days without time entries, that day is not counted. And I don't know how to address it.

Until now I tryed different approaches, but without any success.

One of them was to create a daily summary of the Time entries table and then crossjoin it with the calendar table with 

Daily Timesheet = SUMMARIZE(CROSSJOIN( 'Date', 'Time Entry' ), 
    [Date], 
    [Tech.Id]
)

Then adding new calculated columns for "Technician Country", "Is Not Working" and "Standard Time" with: 

TechCountry = RELATED(Technician[Tech Country])

Is Not Working = IF(
    RELATED('Date'[WeekDayNumber]) > 4 ||  //a day in the weekend
    SUMX('Holiday Dates', FIND([Date] & [TechCountry], 'Holiday Dates'[Holiday Date] & 'Holiday Dates'[HolidayCountry],,0)) > 0, //an holiday day in the technician country 
    TRUE, 
    FALSE
    )
Standard Time = IF([Is Not Working] = TRUE, 
    0,
    IF(
        ISBLANK(RELATED(Technician[Max Daily Hours])), 
        8, // We assume a standard 8 working hours per day
        RELATED(Technician[Max Daily Hours])
    )
)

And finally creating the missing columns through LOOKUP to the original time entries table, like e.g.

Documented Time = LOOKUPVALUE('Time Entry'[Daily Documented Time],'Time Entry'[Tech.Id],[Tech.Id],'Time Entry'[Date Start],[Date],0)

 The main issue with this approach is the size of the cross joined daily summary table and the fact that I have to work with columns and not with measures.

I hope this can clarify.

maclura

[v-chenwuz-msft]

Hi 

I will try to solve your problem and I will update it here when I have results.

Best Regards

Community Support Team _ chenwu zhu

[maclura · Accepted solution]

Hi @v-chenwuz-msft ,

My conclusion is that you can't do that with DAX, and if you can, it's not the best approach.

Thanks to this post and to the Karimkz's reply I concluded that you have to solve it in Power query M.

So I merged (leftouter) my calendar table with the technician table, and in this way I've got all the dates I need.

Then all the DAX calculations have become easy and clean and neat.

Thanks for all your support.

maclura