Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating Elapsed Business Hours Using DAX

Hello!   Need some help to calculate Business Hours using Dax. I found the solution i almost need here:   Calculating Elapsed Business Hours Using DAX Part 2 - Excelerator BI   Could you pleas...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    Try this formula

    Business Hours_2 =
VAR _Start = 'Incident Fact'[createdon]
VAR _End = 'Incident Fact'[Case_Solved]
VAR _Country = 'Incident Fact'[Country]
VAR _Workhours =
    SUMX (
        CALCULATETABLE (
            'Calendar',
            DATESBETWEEN ( 'Calendar'[Date], _Start, _End ),
            SWITCH (
                _Country,
                "Spain", 'Calendar'[Working Day Spain] = 1,
                "Portugal", 'Calendar'[Working Day Portugal] = 1
            )
        ),
        MAX ( MIN ( 'Calendar'[End], _End ) - MAX ( 'Calendar'[Start], _Start ), 0 ) * 24
    )
RETURN
    IF ( _Workhours, _Workhours, 0 )

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi Anonymous 

 

Try this formula

Business Hours_2 =
VAR _Start = 'Incident Fact'[createdon]
VAR _End = 'Incident Fact'[Case_Solved]
VAR _Country = 'Incident Fact'[Country]
VAR _Workhours =
    SUMX (
        CALCULATETABLE (
            'Calendar',
            DATESBETWEEN ( 'Calendar'[Date], _Start, _End ),
            SWITCH (
                _Country,
                "Spain", 'Calendar'[Working Day Spain] = 1,
                "Portugal", 'Calendar'[Working Day Portugal] = 1
            )
        ),
        MAX ( MIN ( 'Calendar'[End], _End ) - MAX ( 'Calendar'[Start], _Start ), 0 ) * 24
    )
RETURN
    IF ( _Workhours, _Workhours, 0 )

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Alpesh's avatar
Alpesh
Helper I
2 years ago

Hi, I know this is an old thread, but how could the same be modified for every country, where most countries have different start and end times and holidays?