Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate solve complaints days without Weekends

Hello All, I was trying to calculate the number of days we are taking to solve a case excluding the weekends with DAX but it is showing all blank can you please help with Power Query or dax to get t...
Need Help
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, you may create two measures like below. The pbix file is attached in the end.

    Measure 2 = 
var tab = 
ADDCOLUMNS(
    ALL(Table1),
    "End",
    SWITCH(
        [Status],
        "Resolved",
        IF(
            ISBLANK([Resolved Date]),
            TODAY(),
            [Resolved Date]
        ),
        "Active",TODAY(),
        [Created On]
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Count",
    COUNTROWS(
        FILTER(
            CALENDAR(
                [Created On],
                [End]
            ),
            NOT(WEEKDAY([Date]) in {1,7})
        )
    )
)
return
SUMX(
    newtab,
    [Count]
)

     

    solved day3 measure = 
var tab = 
ADDCOLUMNS(
    ALL(Table1),
    "Count",
    var _end = 
    IF(
        ISBLANK([Solved Date]),
        TODAY(),
        [Solved Date]
    )
    return 
    IF(
        ISBLANK([Created On]),
        0,
        COUNTROWS(
            FILTER(
                CALENDAR(
                    [Created On],
                    _end
                ),
                NOT(WEEKDAY([Date]) in {1,7}) 
            )
        )
    )
)
return
SUMX(
    tab,
    [Count]
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Anonymous , it requires a separate calendar table for excluding weekends; thus created a separate calculated table,

 

KalendaR = CALENDARAUTO()

 

and measure,

 

Days Elapsed =
VAR __st = MAX ( Table1[Created On] )
VAR __end =
    SWITCH (
        MAX ( Table1[Status] ),
        "Resolved", MAX ( Table1[Solved Date] ),
        "Active", TODAY (),
        __st
    )
RETURN
    COUNTROWS (
        FILTER (
            KalendaR,
            __st <= KalendaR[Date] && KalendaR[Date] <= __end
                && WEEKDAY ( KalendaR[Date], 2 ) < 6
        )
    )