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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ERAHUMO
Frequent Visitor

Measure to count consecutive working days with condition on other column

I have following table in power bi:

 

 

Namedaterating
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:

 

 

NamedateratingConsecutive_Days
abhishek.bacchan12/2/2022A10
abhishek.bacchan12/16/2022A10
abhishek.bacchan12/19/2022A10
abhishek.bacchan12/22/2022A10
abhishek.bacchan1/20/2023A20
abhishek.bacchan1/24/2023A30
abhishek.bacchan1/30/2023A40
abhishek.bacchan1/31/2023A42
abhishek.bacchan3/27/2023A10
abhishek.bacchan3/28/2023A11
abhishek.bacchan3/29/2023A12
abhishek.bacchan4/4/2023A10
abhishek.bacchan4/6/2023A10
abhishek.bacchan4/10/2023A10
abhishek.bacchan4/12/2023A10
abhishek.bacchan4/19/2023A10
abhishek.bacchan5/8/2023A10

 

 

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

 

 

4 REPLIES 4
Fowmy
Super User
Super User

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

 

Fowmy_0-1703757300164.png
File is attached:


 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

TomMartens
Super User
Super User

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:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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