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 the desire results?

Thanks

Measure = COUNTROWS(FILTER(ALL(incidents),incidents[Createdon Date] >=SELECTEDVALUE(incidents[Createdon Date]) && incidents[Createdon Date] <=SELECTEDVALUE(incidents[cmx_solveddate]) && NOT(incidents[weekdaysnumber] in {6,7}) && incidents[IsworkingDay] = TRUE()))

 

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.

7 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Do you have a dummy dataset you can share please? maybe ping over a onedrive link. 

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

         

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Anonymous you need to change the dates in your tables to DATE format, not DATE/TIME. 
    Turn off automatic Time Intelligence, then fix your calculated columns to not use the .[Date] syntax.

    Then you should probably relate the Date[Date] field from a date table, marked as such, to [Created on] but hard to say because the fields and table in your sample measure above are not what are in the PBIX file you posted.

     

    I couldn't get very far because your source data needs to be modified (first point above) and that is in Power Query where I don't have access to your source data.