Forum Discussion

MTrullàs's avatar
MTrullàs
Icon for Helper III rankHelper III
4 years ago
Solved

working days

 Hello, I have a problem with this metric. I need to count the working days between the two dates. In some moments, the metric works perfectly, but in other situations, it does not work like in th...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi MTrullàs 
    It is difficult to verify how columns are evaluated within the filter context without having the data in front of your eyes. However, I would follow a totally different approach. I would create my own calendar table. The only thing is that I don't know what are you expecting to see if either of the start/end dates is blank? I just return zero.

    Transporte Campa =
VAR StartDay =
    MAX ( HIFA[Ch35 (WERK-AUS) Ist] )
VAR EndDay =
    MAX ( HIFA[Ch38 (DEP-EIN) Ist] )
RETURN
    IF (
        ISBLANK ( StartDay ) || ISBLANK ( EndDay ),
        0,
        VAR DatesTable =
            CALENDAR ( StartDay, EndDay - 1 )
        VAR DatesAndWeekDays =
            ADDCOLUMNS ( DatesTable, "@WeekDay", WEEKDAY ( [Date], 2 ) )
        VAR WorkDaysTable =
            FILTER ( DatesAndWeekDays, NOT ( [@WeekDay] IN { 6, 7 } ) )
        RETURN
            COUNTROWS ( WorkDaysTable )
    )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi MTrullàs 
It is difficult to verify how columns are evaluated within the filter context without having the data in front of your eyes. However, I would follow a totally different approach. I would create my own calendar table. The only thing is that I don't know what are you expecting to see if either of the start/end dates is blank? I just return zero.

Transporte Campa =
VAR StartDay =
    MAX ( HIFA[Ch35 (WERK-AUS) Ist] )
VAR EndDay =
    MAX ( HIFA[Ch38 (DEP-EIN) Ist] )
RETURN
    IF (
        ISBLANK ( StartDay ) || ISBLANK ( EndDay ),
        0,
        VAR DatesTable =
            CALENDAR ( StartDay, EndDay - 1 )
        VAR DatesAndWeekDays =
            ADDCOLUMNS ( DatesTable, "@WeekDay", WEEKDAY ( [Date], 2 ) )
        VAR WorkDaysTable =
            FILTER ( DatesAndWeekDays, NOT ( [@WeekDay] IN { 6, 7 } ) )
        RETURN
            COUNTROWS ( WorkDaysTable )
    )