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
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.
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://www.youtube.com/watch?v=GR9ROCQVyLk
Thanks in advnace.
Regards
BVK
Solved! Go to Solution.
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)
)
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!
@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.
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!
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)
)
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!
@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.
@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!
@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.
@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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
107 | |
92 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |