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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Amit,

 

thanks for your response, i used the way you mentioned as well, it works, but the issue i face is how to include the following condition into DAX.

I have the 'Incident_Fact' Table with all the cases and there is column with countries in which the case was created.

 

And i have the 'Calendar' Table where columns per country indicate if this particular Date is holiday or Weekend. 

 

 

 

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

 

 

I struggle to put this condition in the DAX above. It should be something like:
 
If 'Incident'[country_name] = "Spain" then use 'Calendar'[Working Day Spain] =1, 
If 'Incident'[country_name] = "Portugal" then use 'Calendar'[Working Day Portugal] =1 etc
 
Thanks
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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    It works, great. Thanks for help!

  • 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?