Case Age between two dates excluding holiday and weekends

[amabrenda1986]

Hello

I want to:

a. calculate the case age between two dates (created on Date and Completed On date), if the status = Close or Resolved or Cancelled Or

b. Calculate the case age between two dates (created On and Today), if the status = Active

in Power BI, with the exclusion of holidays and Weekends.

Please note that I have a table which i have created a calendar table where I have specified the workdays and holidays and weekends. See screen shot below.

Specifically, can someone please help me with the formula to calculate the Case Age in Power BI?

THANK YOU IN ADVANCE

[v-binbinyu-msft · Accepted solution]

Hi @amabrenda1986 ,

Please try below steps:

1. you need create a date table that record the holiday date, i create a holiday Table 2 for test

Table 2:

Table:

2. create a new column for Table with below dax formula

Column =
VAR _status = [StatusCode]
VAR _start = [CreatedOn]
VAR _end = [CompletedOn]
VAR tmp =
    SELECTCOLUMNS ( 'Table 2', "Date", [Date] )
VAR _val =
    SWITCH (
        [StatusCode],
        "Active", NETWORKDAYS ( _start, TODAY (), 1, tmp ),
        "Resolved", NETWORKDAYS ( _start, _end, 1, tmp ),
        "Cancelled", NETWORKDAYS ( _start, _end, 1, tmp )
    )
RETURN
    _val

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amabrenda1986]

Please can someone tell me what is wrong with this query

Age = IF(Intakes[value.statecode@OData.Community.Display.V1.FormattedValue]="Active",

IF(CALENDAR([Work Hours]>0,

DATEDIFF(Intakes[value.createdon@OData.Community.Display.V1.FormattedValue],TODAY(),DAY))

it gives an error message

[amabrenda1986]

I used this formular but how do i exclude holidays and Weekends please

Age = IF(Intakes[value.statecode@OData.Community.Display.V1.FormattedValue]="Active",

DATEDIFF(Intakes[value.createdon@OData.Community.Display.V1.FormattedValue],TODAY(),DAY),

DATEDIFF(Intakes[value.createdon@OData.Community.Display.V1.FormattedValue],Intakes[value.coc_completedon@OData.Community.Display.V1.FormattedValue],DAY))

[amabrenda1986]

See sample extract of my fact table

exctract from date table 

[Greg_Deckler]

@amabrenda1986 That just looks like a date table. What does your fact table look like? Normally these days you would use the NETWORKDAYS function.

[amabrenda1986]

I am expecting a formula that will

1. calculate the date difference between the created date and today, with the exclusion of holidays and weekends if the status = Active

2. Calculate the date difference between created date and completed date, with the exclusion of holidays and weekends if the status = Completed or cancelled

[amabrenda1986]

my fact table has a created on date and completed on date with the statuses. 

hope this information is sufficient for you to give me an answer please