cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 :

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.

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

Regards

BVK

1 ACCEPTED SOLUTION
Continued Contributor

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

9 REPLIES 9
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:

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

Proud to be a Super User!

Continued Contributor

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"

Super User

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.

Proud to be a Super User!

Continued Contributor

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

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

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!

Continued Contributor

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.

Super User

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

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!

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

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?

Super User

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

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!