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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
itsmebvk
Continued Contributor
Continued Contributor

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

 

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 : 

 

itsmebvamsi_1-1674199257833.png

 

 

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. 

 

itsmebvamsi_0-1674199200396.png

 

Example Data : 

 

itsmebvamsi_2-1674199331265.png

 

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

1 ACCEPTED SOLUTION
itsmebvk
Continued Contributor
Continued Contributor

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

 

 

 

View solution in original post

9 REPLIES 9
bolfri
Super User
Super User

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:

bolfri_0-1674230511051.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




itsmebvk
Continued Contributor
Continued Contributor

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

 

 

 

 

itsmebvamsi_1-1674283256891.png

 

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

 

itsmebvamsi_2-1674283596286.png

 

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.

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:

bolfri_0-1674306571737.png

 

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.

 

bolfri_1-1674306665597.png

 

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

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




itsmebvk
Continued Contributor
Continued Contributor

@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
Super User
Super User

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 )

ERD_0-1674031070565.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

itsmebvk
Continued Contributor
Continued Contributor

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

 

itsmebvamsi_1-1674090757637.png

 

 

itsmebvamsi_0-1674090595402.png

 

 

 

Please suggest workarounds to achieve this requirement.

 

Thank you again.

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

ERD_1-1674232907867.png

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).

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

itsmebvk
Continued Contributor
Continued Contributor

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

 

 

 

 

 

itsmebvamsi_4-1674284856199.png

 

 

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)

 

itsmebvamsi_5-1674285160848.png

 

 

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

 

Thanks in advance.

 

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

ERD_1-1674466333141.png

 

ERD_0-1674466142010.png

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

ERD_2-1674466693465.png

 

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

ERD_3-1674466722667.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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