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!