Total count of dates that meet a condition and calculating current date streak.

[dejate]

Hi all,

I'm a beginner with PowerBi and looking for some guidance.

I'm working on a student attendance report and need to simply calculate total sickness "days"  per student.

Each student can have multiple register marks on each day.

As a first step, I need to simply calculate if a student has at least 1 or more "Sick" mark in the day to return a "1" for Sick day.

Data example as follows:

Student	Subject Code	Date	Register Mark
1001	MATH22	22/09/2022	Sick
1001	GEO22	22/09/2022	Sick
1001	PHY22	22/09/2022	Sick
1001	MATH22	21/09/2022	Present
1001	GEO22	21/09/2022	Present
1001	PHY22	21/09/2022	Present
1001	MATH22	18/09/2022	Sick
1001	GEO22	18/09/2022	Present
1001	PHY22	18/09/2022	Present
1001	MATH22	16/09/2022	Present
1001	GEO22	16/09/2022	Present
1001	PHY22	16/09/2022	Present
1002	MATH22	22/09/2022	Present
1002	GEO22	22/09/2022	Present
1002	PHY22	22/09/2022	Present
1002	MATH22	21/09/2022	Present
1002	GEO22	21/09/2022	Present
1002	PHY22	21/09/2022	Present
1002	MATH22	18/09/2022	Present
1002	GEO22	18/09/2022	Present
1002	PHY22	18/09/2022	Present
1002	MATH22	16/09/2022	Sick
1002	GEO22	16/09/2022	Present
1002	PHY22	16/09/2022	Present

In the above data, I would expect to return.

Student	Total Sick days	Current Sickness Streak
1001	2	1
1002	1	0

Once I have calculated total sick days, a trickier challenge will be to calculate the students currently on a "Current Sickness Streak" which is a running total of consecutive sickness with no full present days.

I don't think i can use DateDiff, as the dates are not in consecutive day sequence, eg there are gaps in between when a lesson is held.

Any advice to point me to the best approach would be greatly appreciated.

Thanks in advance

dejate - UK

[Anonymous]

Hi  @dejate ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below to get Total Sick days:

Total Sick days = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Date] ),
    FILTER ( 'Table', 'Table'[Register Mark] = "Sick" )
)

2. Follow the steps below to get Current Sickness Streak

1) Create a calculated column as below to judge if the sickness is steak on one whole day

Flag = 
VAR _ccount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Subject Code] ),
        FILTER (
            'Table',
            'Table'[Student] = EARLIER ( 'Table'[Student] )
                && 'Table'[Date] = EARLIER ( 'Table'[Date] )
        )
    )
VAR _sickcount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Subject Code] ),
        FILTER (
            'Table',
            'Table'[Student] = EARLIER ( 'Table'[Student] )
                && 'Table'[Date] = EARLIER ( 'Table'[Date] )
                && 'Table'[Register Mark] = "Sick"
        )
    )
RETURN
    IF ( _ccount = _sickcount, 1, 0 )

2) Create a measure as below to get Current Sickness Streak

Current Sickness Streak = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Date] ),
    FILTER ( 'Table', 'Table'[Flag] = 1 )
) + 0

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[dejate]

Thnaks for your reply @Anonymous  - Your measure for Total Sick days worked great, thnaks for that.

But the consecutive sick day measure just seems to return a 1 Flag for every day in my data. It maybe my grouping, so still trying to work through this, but thanks for your suggestion.

I'm also working through @amitchandak suggestion from https://youtu.be/GdMcwvdwr0o

Thanks both, Ill post my update 

[Anonymous]

Hi @dejate ,

Thanks for your feedback. So do you get the final solution for your problem now? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[amitchandak]

@dejate ,

refer if this can help Continuous streak: https://youtu.be/GdMcwvdwr0o

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...