Measure to count consecutive working days with condition on other column

[ERAHUMO]

I have following table in power bi:

Name	date	rating
abhishek.bacchan	12/2/2022	A1
abhishek.bacchan	12/16/2022	A1
abhishek.bacchan	12/19/2022	A1
abhishek.bacchan	12/22/2022	A1
abhishek.bacchan	1/20/2023	A2
abhishek.bacchan	1/24/2023	A3
abhishek.bacchan	1/30/2023	A4
abhishek.bacchan	1/31/2023	A4
abhishek.bacchan	3/27/2023	A1
abhishek.bacchan	3/28/2023	A1
abhishek.bacchan	3/29/2023	A1
abhishek.bacchan	4/4/2023	A1
abhishek.bacchan	4/6/2023	A1
abhishek.bacchan	4/10/2023	A1
abhishek.bacchan	4/12/2023	A1
abhishek.bacchan	4/19/2023	A1
abhishek.bacchan	5/8/2023	A1

I am trying to identify max count of consecutive working days in for each employee who has a specific rating, in this example A1

The resultant table should look something like this:

Name	date	rating	Consecutive_Days
abhishek.bacchan	12/2/2022	A1	0
abhishek.bacchan	12/16/2022	A1	0
abhishek.bacchan	12/19/2022	A1	0
abhishek.bacchan	12/22/2022	A1	0
abhishek.bacchan	1/20/2023	A2	0
abhishek.bacchan	1/24/2023	A3	0
abhishek.bacchan	1/30/2023	A4	0
abhishek.bacchan	1/31/2023	A4	2
abhishek.bacchan	3/27/2023	A1	0
abhishek.bacchan	3/28/2023	A1	1
abhishek.bacchan	3/29/2023	A1	2
abhishek.bacchan	4/4/2023	A1	0
abhishek.bacchan	4/6/2023	A1	0
abhishek.bacchan	4/10/2023	A1	0
abhishek.bacchan	4/12/2023	A1	0
abhishek.bacchan	4/19/2023	A1	0
abhishek.bacchan	5/8/2023	A1	0

Now the reason why it says 2 in Consecutive_Days is because it has 2 days in sequence before that. (1/30/2023, 1/31/2023)

i tried a couple of DAX codes but unable to attain the required output. The below code comes close to the same but it seems it is resetting the count every week hence the max count is 5. Ideally if name has recorded for 2 weeks consecutively (Monday-Friday, Monday-Friday) then it should show 10 instead of 5 and 5 since we are excluding weekends.

MaxConsecutiveWorkdays = 
VAR MaxConsecutive =
MAXX (
ADDCOLUMNS (
SUMMARIZE (
'Tablename',
'Tablename'[Name],
'Tablename'[date],
"ConsecutiveWorkdays",
SUMX (
FILTER (
ALLSELECTED('Tablename'),
'Tablename'[rating]<>"A1"
&& WEEKDAY ('Tablename'[date], 2) < 6
&& 'Tablename'[date] = EARLIER('Tablename'[date]) + RANKX( FILTER(ALLSELECTED('Tablename'), 'Tablename'[Name] = EARLIER('Tablename'[Name])), 'Tablename'[date], , ASC)
),
1
)
),
"MaxConsecutive",
MAXX (
FILTER (
ALLSELECTED('Tablename'),
'Tablename'[rating]<>"A1"
&& WEEKDAY ('Tablename'[date], 2) < 6
&& 'Tablename'[date] = EARLIER('Tablename'[date]) + RANKX( FILTER(ALLSELECTED('Tablename'), 'Tablename'[Name] = EARLIER('Tablename'[Name])), 'Tablename'[date], , ASC)
),
[ConsecutiveWorkdays]
)
),
[MaxConsecutive]
)
RETURN
MaxConsecutive

[Fowmy]

@ERAHUMO 

Based on the data, the explanation and as per my understanding, I created a measure to calculate the maximum consecutive days by name and rating. In your example, I did not understand why you are showing 2 days for the period 1/30/2023 to 1/31/2023 but from 27/3/2023 to 29/3/2023, you still show 2 days . Should it be 1 and 2 for these two cases accordingly or 2 and 3 days.

My measure:

Max Days = 
MAXX(
    ADDCOLUMNS(
        VALUES( 'Dates'[Date] ),
        "Days",
        VAR __CurrentDate = 'Dates'[Date] 
        VAR __DatesFull = ALLSELECTED( 'Dates'[Date] )
        VAR __CurrentDateRange = CALCULATETABLE( VALUES( 'Table'[date] ) , ALLSELECTED('Dates' ) )
        VAR __DatesWithData = EXCEPT( __DatesFull , __CurrentDateRange)
        VAR __LastDateWithData = MAXX(  FILTER( __DatesWithData , 'Dates'[Date] <= __CurrentDate ), Dates[Date])
        RETURN
        IF( NOT ISEMPTY( 'Table') ,  INT( __CurrentDate - __LastDateWithData )-1 )
    )
    ,[Days]
)

File is attached:

[TomMartens]

Hey @ERAHUMO ,

I think you have to use a Calendar table to find the number of consecutive working days.

The attached pbix contains a very simple Calendar table.

The Calendar table is created based on Start Date, the Start Date defines the first day of the Calendar table.

The Calendar stops at the 31st of December the year after today.

Please be aware that my week starts with a Monday, for this reason you maybe have to adjust the formula that is used to determine the value of the column "Day of week."

This column is used to determine the values for subsequent columns "Is Working Day" and "Is Weekend".

Personally, I tend to use index columns, counting the number of days in a year or across the complete calendar. The column "idx WorkingDay by Year" represents the running number of working days in a given calendar year:

Please notice the highlighted values. On weekends (non-working days), the last number of working days is repeated; for the first two days of the Calendar year 2022, this value is 0 because there are no previous working days.

The running number of working days is determined by subtracting the cumulated number of non-working days from the day of the year value.

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[ERAHUMO]

@TomMartens Thanks for sharing the approch. I can use this but i need help in creating a measure which can work with my sample data. From what i understand it surely isnt straightforward to achieve my end goal. Can you share any further information on the same?

[TomMartens]

Hey @ERAHUMO ,

determining the number of consecutive "somethings", here it's workingdays, is one of the challenges of the larger area called gaps and islands. This article provides a gentle intoduction, though it's about SQL: https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/introduction-...

To be honest, I'm currently not aware of a general pattern for DAX, and I do think it's very hard to find a solution with a one-table solution. This is the reason, why I will not spend thinking about a DAX statement that works with your sample data.

Regards,

Tom