[Dax] Count consecutive days but ignore weekends

[Anonymous]

Hi, 

I need to calculate how many consecutive days the ticket (with a specific case number).

I've found a formula here very similar to what I need, but it is considering the weekends, which I don't want to consider, I want it to follow the sequence:

For example, on 11th July, it is a Monday, so I want the Aging to be 2 and continue the sequence until the next day out of the sequence (in this case on 20th July it is 1 again as it should be). 

This is the formula:

Aging =

VAR vCurrentDate = Append1[ReportDate]

VAR vCurrentID =  Append1[CaseNumber]

VAR vPrevTbl =

    FILTER ( Append1,  Append1[ReportDate] <= vCurrentDate && Append1[CaseNumber] = vCurrentID )

VAR vPrevDate =

    MAXX (

        FILTER (

            vPrevTbl,

            VAR vCD =  Append1[ReportDate]

            VAR r =

                MAXX ( FILTER ( vPrevTbl,  Append1[ReportDate] < vCD ),  Append1[ReportDate] )

            RETURN

                vcd - 1 <> r

        ),

         Append1[ReportDate]

    )

RETURN

      vCurrentDate - vPrevDate +1

I've already got the calendar table with weekends and tried to adapt the formula but can't get the result.

Could you please help? Thank you 

[v-binbinyu-msft · Accepted solution]

Hi @Anonymous ,

Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.

Please try below steps

1. below is my test table

Table:

2. add a column to help calculate

Index_By_CaseNumber =
VAR cur_casenumber = 'Table'[CaseNumber]
VAR tmp =
    FILTER ( 'Table', 'Table'[CaseNumber] = cur_casenumber )
RETURN
    RANKX ( tmp, [Date],, ASC )

3. add a new calumn to calculate the days

Column =
VAR cur_date = 'Table'[Date]
VAR cur_casnumber = 'Table'[CaseNumber]
VAR cur_index = 'Table'[Index_By_CaseNumber]
VAR pre_date =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Date] ),
        'Table'[CaseNumber] = cur_casnumber,
        'Table'[Index_By_CaseNumber] = cur_index - 1,
        ALL ( 'Table' )
    )
VAR diff_for_day =
    DATEDIFF ( pre_date, cur_date, DAY )
RETURN
    SWITCH ( diff_for_day, BLANK (), cur_index, 1, cur_index )

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.