How to Calculate Consecutive Days of absence with Reset Number - Different Logic

[itsmebvk]

Hi Folks,

I am trying to create a measure called " Absence Consecutive Days" which calculates how many consecutive days of absence occurred during last seven days (Excluding today) . I have used similar kind of measure in other reports to track consecutive days absence but this report logic is different, here i want to reset the number of consecutive absence to 0 if attendance records between seven days.

PBIX and Sample Data

Desired Output : 

Reset Number When Attendance Recorded:

In the following example even though student 1 is absent to computer on 11/01/2023,12/01/2023 since he attended on 13/01/2023 we need to reset the number to 0 and again look for consecutive absence after that date. 

Example Data : 

Can you please suggest any work around to recreate this measures.  Please find the attched sampel data for your refrence.

Note: I have already referred the blogs mentioned below.

https://community.powerbi.com/t5/Desktop/Count-Consecutive-Days-Worked/td-p/2052307

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-find-consecutive-days-with-a-sale/m-p/...

https://www.youtube.com/watch?v=GR9ROCQVyLk

Thanks in advnace.

Regards

BVK

[bolfri]

Measure:

Absence Constructive Days = 
var min_possible_date = FIRSTDATE(Data[Date])
var max_possible_date = LASTDATE(Data[Date])
var last_attended_date = CALCULATE(LASTDATE(Data[Date]),FILTER(Data,Data[Attendence]="Attend"))
var constructive_days = DATEDIFF(IF(ISBLANK(last_attended_date),min_possible_date-1,last_attended_date),max_possible_date,DAY)
return IF(HASONEVALUE(Data[Date]),SELECTEDVALUE(Data[Attendence]),constructive_days)

Results:

Description:

min_possible_date - first date for current row context that appears in data (if there is no attendence for student, we will be counting from this date)

max_possible_date - last date for current row context that appears in data (if there is no attendence for student, we will be counting to this date)

last_attended_date - last date that student shows up on cource (may be blank when student never show up)

constructive_days is your variable - it calculates the difference between last attended date till max_possible_date and returns in total

return statemens checks if we are in column or total context to show the "Attend" or number in the matrix

[itsmebvk]

@bolfri  Thanks for your reply.

I tried the suggested Dax measure with my original data (in realtime Course i have taken from course Dlmension table and Student i have taken  from Student Dimension table. And Attendance" column contains numbers instead "Attend" status), but when i tweak the measure I am getting "0"in all rows instead of numbers 

Do you think its because of I am using Course and Student from Dimension tables?

Updated Dax for Original Data:

Absence Constructive Days = 
var min_possible_date = FIRSTDATE(Data[Date])
var max_possible_date = LASTDATE(Data[Date])
var last_attended_date = CALCULATE(LASTDATE(Data[Date]),FILTER(Data,Data[Attendence]>0))
var constructive_days = DATEDIFF(IF(ISBLANK(last_attended_date),min_possible_date-1,last_attended_date),max_possible_date,DAY)
return IF(HASONEVALUE(Data[Date]),[Attendence],constructive_days)

And also another important thing is I want to filter the output to show only the rows where "Total">0 like below

Can you please suggest where I am doing wrong? and also suggest how to filter rows to show which are greater than "0" 

Thanks in advance.

[bolfri]

Your DAX was correct. I've took your data from before and changed the Attencende from text to random number, so my sample data looks like this:

New measure for this data will be like this:

Absence Constructive Days = 
var min_possible_date = FIRSTDATE(Data[Date])
var max_possible_date = LASTDATE(Data[Date])
var last_attended_date = CALCULATE(LASTDATE(Data[Date]),FILTER(Data,Data[Attendence]<>0))
var constructive_days = DATEDIFF(IF(ISBLANK(last_attended_date),min_possible_date-1,last_attended_date),max_possible_date,DAY)
return IF(HASONEVALUE(Data[Date]),SELECTEDVALUE(Data[Attendence]),constructive_days)

Note that the only change here is: FILTER(Data, Data[Attendence] <> 0)

For filtering out you can use Filters on visual with the measure not 0.

PBIX file with working solution: https://we.tl/t-uBDbA75LIH

Remember to accept responses as solution, so everyone can find answers for similar problems.

[itsmebvk · Accepted solution]

@ERD @bolfri 

Thanks alot both of you for your inputs and taking time to look into it. 

With the help of your inputs, I was able to achieve the requirement. I just tweaked the DAX you both suggested to cater some of the scenarios based on my original data.

Thanks alot again.

Consecutive Days = 

Var _Attendance = CALCULATE([Attendance],ALLSELECTED(Fact[Calendar_Dt]))    

VAR last_non_absent = IF(_Attendance>0,CALCULATE(LASTDATE(Fact[Calendar_Dt]),FILTER(Fact,[Attendance]<>0)),TODAY()-8)

VAR first_absent = last_non_absent+1

RETURN  
     IF( 
        HASONEVALUE ( Dim_Date[Calendar_Date] ), 
        [Attendance] , 
        DATEDIFF ( first_absent,TODAY(),DAY)
    )
	

[ERD]

Hello @itsmebvk ,

You can try this measure:

Consecutive Days =
VAR last7days =
    FILTER ( Dim_Date, Dim_Date[Date] < TODAY () && Dim_Date[Date] >= TODAY () - 7 )
VAR last_non_absent =
    MAXX (
        FILTER ( Data, Data[Attendence] <> BLANK () && Data[Date] IN last7days ),
        Data[Date]
    )
VAR first_absent =
    MINX (
        FILTER (
            Data,
            Data[Attendence] = BLANK ()
                && Data[Date] > last_non_absent
                && Data[Date] IN last7days
        ),
        Data[Date]
    )
RETURN
    DATEDIFF ( first_absent, TODAY (), DAY )

[itsmebvk]

@ERD  Thanks alot for your reply.

First of all let me apologise for not providing requirement in simple way. I have updated the original post with simple screenshots with sample data. 

Can you please try the sample data updated and help me in fixing the issue.

Note : I also tried the solution provided earlier, however I am getting following error when I add this measure to table.

I am using Dim_Date table and Fact_Table from Power BI Dataset as source (Live Connection)

Please suggest workarounds to achieve this requirement.

Thank you again.

[ERD]

@itsmebvk , yes, I see, your sample had 1 column in Date table, but in reality it seems to be more. It's a small change in a mesaure:

Consecutive Days = 
VAR last7days =
    FILTER ( ALL('Dim_Date'[Date]), 'Dim_Date'[Date] < TODAY () && 'Dim_Date'[Date] >= TODAY () - 7 )
VAR last_non_absent =
    MAXX (
        FILTER ( Data, Data[Attendence] <> BLANK () && Data[Date] IN (last7days) 
        ),
        Data[Date]
    )
VAR first_absent =
    MINX (
        FILTER (
            Data,
            Data[Attendence] = BLANK ()
                && Data[Date] > last_non_absent
                && Data[Date] IN last7days
        ),
        Data[Date]
    )
RETURN 
    IF( 
        HASONEVALUE ( Data[Date] ),
        SELECTEDVALUE ( Data[Attendence] ),
        DATEDIFF ( first_absent, TODAY () ), DAY )
    )

The measure assumes you're using 2 tables in your visual as you've mentioned before: Dim_Date table and Fact_Table (here Dim_Date and Data), and the period is last seven days (Excluding today).

For testing, to see the result with the dates provided (more then 1 week actually, today's date - 20/1/2023), change TODAY () to DATE(2023,1,14).

[itsmebvk]

@ERD Thanks for looking into this again. 

I have tried the suggested DAX measure with original data, (In realtime Course i have taken from course Dlmension table and Student i have taken  from Student Dimension table. And Attendance" column contains numbers instead "Attend" status.)

I have updated Dax based on original Data:

	Consecutive Days = 
VAR last7days =
    FILTER ( ALL('Dim_Date'[Date]), 'Dim_Date'[Date] < TODAY () && 'Dim_Date'[Date] >= TODAY () - 7 )
VAR last_non_absent =
    MAXX (
        FILTER ( Data, Data[Attendence] >0 && Data[Date] IN (last7days) 
        ),
        Data[Date]
    )
VAR first_absent =
    MINX (
        FILTER (
            Data,
            Data[Attendence] = 0
                && Data[Date] > last_non_absent
                && Data[Date] IN last7days
        ),
        Data[Date]
    )
RETURN 
    IF( 
        HASONEVALUE ( Data[Date] ),
        SELECTEDVALUE ( Data[Attendence] ),
        DATEDIFF ( first_absent, TODAY () ), DAY )
    )

When I try this Dax I am getting values in row cells but "Total"column i showing blank. Do you think its because of I am using Course and Student from Dimension tables?

Aanother important  issues is I am unable to filter total column. I want to show rows where Total row is >0 (As below)

Can you please correct my mistake and suggest way to filter based on total column?

Thanks in advance.

[ERD]

@itsmebvk , if you need to include a period in the last seven days (Excluding today), as you've mentioned before, and use dimension tables in the visual (Dim_Date, CourseDim, StudentDim) + filter out rows with total = 0:

Consecutive Days = 
VAR last7Days = 
    FILTER ( 
        DISTINCT('Dim_Date'[Date]), 
        'Dim_Date'[Date] < TODAY() && 'Dim_Date'[Date] >=  TODAY() - 7 
    )
VAR last_non_absent = MAXX ( FILTER ( Data, Data[Attendence] <> 0 ), Data[Date] )
VAR first_absent =
    MINX (
        FILTER (
            Data, 
            Data[Attendence] = 0 && Data[Date] IN last7days && Data[Date] > last_non_absent  
        ),
        Data[Date]
    )
RETURN 
    IF( 
        HASONEVALUE ( Dim_Date[Date] ), 
        SELECTEDVALUE(Data[Attendence]), 
        DATEDIFF ( first_absent, TODAY(), DAY)
    )

For testing, to see the result with the dates provided (more then 1 week actually, today's date - 23/1/2023), change TODAY () to DATE(2023,1,14).

If you need to include a period in the last seven days starting from the latest available date with some data, and use dimension tables in the visual (Dim_Date, CourseDim, StudentDim) + filter out rows with total = 0:

Consecutive Days v2 = 
VAR latestDateWithData = MAX(Data[Date])
VAR last7DaysFromMaxDate = 
    FILTER ( 
        DISTINCT('Dim_Date'[Date]), 
        'Dim_Date'[Date] <= latestDateWithData && 'Dim_Date'[Date] >=  latestDateWithData - 6 
    )
VAR last_non_absent =
    MAXX (
        FILTER ( Data, Data[Attendence] <> 0 ),
        Data[Date]
    )
VAR first_absent =
    MINX (
        FILTER (
            Data,
            Data[Attendence] = 0
                && Data[Date] IN last7DaysFromMaxDate
                && Data[Date] > last_non_absent
        ),
        Data[Date]
    )
RETURN 
    IF( 
        HASONEVALUE ( Dim_Date[Date] ),
        MAX(Data[Attendence]),
        DATEDIFF ( first_absent, latestDateWithData+1, DAY )
    )

Data source is slightly changed (contains 1 instead "Attend" status, otherwise 0😞