Calculate solve complaints days without Weekends

[Anonymous]

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

[v-alq-msft · Accepted solution]

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.

[edhans]

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

[Anonymous]

@edhans yes, i did try after changing the format but no luck. Here I am sharing with you the updated report link or if you can share any sample with the required measure would be a great help

https://1drv.ms/u/s!Aj7YLBYfrzPkilxirwLzkJW_BVe5?e=Hpa60s

[Anonymous]

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

[Anonymous]

Please do let me know if you able to view the information or not?

https://1drv.ms/u/s!Aj7YLBYfrzPkihemTZdczXX5R9Ns?e=bZDTni

[CNENFRNL]

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

[Anonymous]

Its showing Blank, could you please help me to create a measure in the shared file? 

https://1drv.ms/u/s!Aj7YLBYfrzPkilxirwLzkJW_BVe5?e=toB5Np