Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

[Dax] Count consecutive days but ignore weekends

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:

dbs123_0-1661252825046.png

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 

 

 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

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:

vbinbinyumsft_0-1661479424931.png

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 )

vbinbinyumsft_1-1661479517958.png

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 )

vbinbinyumsft_2-1661479622105.png

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.

View solution in original post

1 REPLY 1
v-binbinyu-msft
Community Support
Community Support

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:

vbinbinyumsft_0-1661479424931.png

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 )

vbinbinyumsft_1-1661479517958.png

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 )

vbinbinyumsft_2-1661479622105.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.