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.

v-alq-msft's avatar
v-alq-msft
Community Support
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.