periods of consecutive instances

[MM_DATA]

HI

Hoping someone can help me out 

i have this table  below. 

i want to be able to have a DAX MEASURE that works of periods of consecutive instances - excluding weekends. So in this example it should show 4 periods of absences. 

any help would be much appreciated 

[MM_DATA]

Sorry not working - when i add employ 2 who is off on friday 31st am and pm and monday am, it gives 2 period of absences. it should be 1 as its on continious absence (ignoring weekends)

[v-priyankata · Accepted solution]

Hi @MM_DATA 
Please find the file below. Based on my understanding, I have reproduced your issue. Kindly try it from your side; if it works, that's great. If not, please share the sample data so we can look into it further.

Thanks.

[MM_DATA]

thank you. i will and will report back. thank you

[v-priyankata]

Hi @MM_DATA 
Could you let me know if it works for you? If you encounter any issues, please contact the community for assistance.

Thanks.

[v-priyankata]

Hi @MM_DATA 
Can you please let us know if it works for you? If you encounter any issues, please contact the community for assistance.

Thanks.

[v-priyankata]

Hi @MM_DATA 

Thank you for reaching out to the Microsoft Fabric Forum Community. @Ashish_Mathur @danextian @Ahmed-Elfeel Thank you so much for your inputs.

The information provided by users was very helpful. Could you let us know if your issue has been resolved? If not, please let us know. We are happy to assist you.

Thanks.

[danextian]

Hi @MM_DATA 

Try the calculated columns in the attached file.

For measure approach, try the other pbix

[Ashish_Mathur]

Hi,

Why should the answer be 4?  Shouldn't it be 2? - 31/10 - 04/11 is 1 instance and then 25/11 - 26/11 is the second instance.

[MM_DATA]

25th is AM

26th is PM 

there is a period 26th AM inbtwen that breaks the consecutive streak

[Ahmed-Elfeel]

Hi @Ashish_Mathur,

As I understood he considers weekends as period breakers:

• Friday → Monday = 2 different periods (weekend in between breaks the continuity)

  • 31/10 = Period 1

  • 03/11-04/11 = Period 2

  • 19/11 = Period 3

  • 25/11-27/11 = Period 4

But It should be 2 (As I and you expect),I thought there was a problem in my calculation but tried until i understood 😅.

[MM_DATA]

got to take into account AM /PM sesisons too.  each day is boken into am and pm

absence on a friday PM and monday AM should be treated as once period of absence .

absence on thursady AM and friday PM should be treated as 2 periods of absences as there is a break between with with no absence on thursady AM. 

hope this makes sense 

[Ahmed-Elfeel]

Hi @MM_DATA,

Could you Try this DAX Formula:

Count Periods Single Measure = 
VAR WeekdayDates =
    FILTER(
        VALUES('Table'[Date]),
        WEEKDAY('Table'[Date], 2) <= 5  // Mon-Fri only
    )
VAR SortedDates =
    ADDCOLUMNS(
        WeekdayDates,
        "PreviousDate",
            VAR CurrentDate = 'Table'[Date]
            RETURN
            CALCULATE(
                MAX('Table'[Date]),
                'Table'[Date] < CurrentDate,
                WeekdayDates,
                ALL('Table')
            )
    )
VAR PeriodStarts =
    FILTER(
        SortedDates,
        ISBLANK([PreviousDate]) ||
        DATEDIFF([PreviousDate], 'Table'[Date], DAY) > 1
    )
RETURN
COUNTROWS(PeriodStarts)

The excepected result for your data should be:

• 31/10/2025 (Friday) - Period 1

• 03/11/2025 (Monday) - Period 2 (gap due to weekend)

• 04/11/2025 (Tuesday) - Same period (consecutive weekday)

• 19/11/2025 (Wednesday) - Period 3 (gap)

• 25/11/2025 (Tuesday) - Period 4 (gap)

• 26/11/2025 (Wednesday) - Same period (consecutive weekday)

The measure will automatically exclude weekends and count only consecutive weekday sequences as single periods, so try it and tell me if you have any more questions ☺️❤️.

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[MM_DATA]

have you created a date table 

please attach a power bi file so i can see the structure of what you have done please

also, its not just date that need to be consecutive but also AM and PM sessions too.  so last absence should not be consecutive as it happened PM the day after therby missing the AM SLOT. 

[Ahmed-Elfeel]

Hi @MM_DATA,

here is the file for the the PBIX file

here is the Screenshots from the file step by step:

First here is the Table from ETL:

Second you should create thte measure that do all the work:

here is the updated DAX measure :

Absence Periods = 
VAR DistinctDates = 
    SUMMARIZE(
        'Table1',
        'Table1'[Employ ID],
        'Table1'[date]
    )
VAR Weekdays = 
    FILTER(
        DistinctDates,
        WEEKDAY('Table1'[date], 2) <= 5
    )
VAR EmployeePeriods = 
    ADDCOLUMNS(
        Weekdays,
        "IsPeriodStart",
            VAR CurrentEmployee = 'Table1'[Employ ID]
            VAR CurrentDate = 'Table1'[date]
            VAR PreviousDate = 
                CALCULATE(
                    MAX('Table1'[date]),
                    FILTER(
                        Weekdays,
                        'Table1'[Employ ID]= CurrentEmployee &&
                        'Table1'[date] < CurrentDate
                    )
                )
            RETURN
                IF(
                    ISBLANK(PreviousDate) || 
                    DATEDIFF(PreviousDate, CurrentDate, DAY) > 1,
                    1,
                    0
                )
    )
RETURN
    SUMX(EmployeePeriods, [IsPeriodStart])

Finally After adding the measure to visual card that shows absence days should be 4:

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[MM_DATA]

hi,

can't down load the powe rbi file you attached 

any chance you can send that through please

[v-priyankata]

Hi @MM_DATA 
Here, I have attached the file that was shared by @Ahmed-Elfeel