Forum Discussion
calculating the consecutive count
Hello,
I am trying to calculate the consecutive absent count of students......like they should be absent for 7 days continuously then the dax should take the count as 1 and if another student is asbsent for continuous 7 days then, dax should increase its count to 2. I have attached the above picture for reference. Consider Dates are stacked in one single column, in the above picture i divided the date into columns for easy understanding.
Help me to create a measure/ calculated column.
3 Replies
Hi YogeshS ,
Please try:
Measure = VAR _a = COUNTX ( FILTER ( 'Table', [Value] = 0 ), [Date] ) VAR _b = CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ), FILTER ( FILTER ( ALL ( 'Table' ), [ID] <= MAX ( 'Table'[ID] ) ), COUNTX ( FILTER ( ALL ( 'Table' ), [ID] = EARLIER ( 'Table'[ID] ) && [Value] = 0 ), [Date] ) = 7 ) ) RETURN IF ( _a = 7, _b )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-jianboli-msft ,
Thank you for your help,
But my data seems to be big, i am getting the below error after implementing your measure.Could you please help me with this?
https://drive.google.com/file/d/1ul3CV3LSPBy5gk8tfmDtrunqCu-eKOsj/view?usp=drive_link
The .Pbix is in the above url
