Meter reader stopped counting - calculate for how many days!

[IEPMost]

Hi guys,

in my table there is [date], [ID], [meter reader] and [status] - when the meter reader stopped counting status switch from "ok" to "stopped". So here is an example:

Date	ID	meter reader	status
01.01.2020	3	200	ok
02.01.2020	3	230	ok
03.01.2020	3	230	stopped
04.01.2020	3	230	stopped
05.01.2020	3	240	ok
06.01.2020	3	240	stopped
07.01.2020	3	240	stopped
08.01.2020	3	240	stopped

Now I want to create a DAX measure, which calculates the number of days the meter reader stopped! In this case the measure should only consider the last 3 days because the day before status displayed "ok". So for how many days the meter reader stopped counting since last status was ok?

Any help is much appreciated!

Cheers!!

[IEPMost]

Hi @v-zhenbw-msft ,

thank you a lot, it works out perfectly now!

[v-zhenbw-msft]

Hi @IEPMost ,

Do you want to show a table visual like this? Just ID and Date count?

If yes, you can put the ID and Date count into Values. And you don’t need to create a measure.

If it doesn’t meet your requirement, could you please show the exact expected result?

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft · Accepted solution]

Hi @IEPMost ,

We can create a calculate column to meet your requirement.

Date count = 
var _max_ok_date = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID]) && 'Table'[status]="ok"))
return
IF(
    'Table'[Date]>_max_ok_date,1,0)

Then we can put the column in a card visual.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[IEPMost]

Hi @v-zhenbw-msft ,

thank you so much for your help! It´s not exactly what I´m looking for because I need the datecount for every ID seperately. So in your example I need the count of 3 for the ID 3 and a count of 4 for ID 4.

I wanted to create a table visual in which you can see the ID and for how many days the meter reader stopped counting (so the status column doesn´t need to be in my visual, only for how many days reader stopped counting). That´s why I wanted to create a DAX Measure. Is this the right way to go?

Thanks again and I hope you know what I mean.

Cheers

[AllisonKennedy]

@IEPMost 

Try something like: 

Days = 

VAR _lastOK = MAXX(FILTER(Table, Table[Reader Status] = "OK"), Table[Date])

RETURN

COUNTROWS(FILTER(Table, Table[Date] > _lastOK))

Depending on how you want to use it, you may need to add in some additional filters like ALL or ALLSELECTED

[IEPMost]

Hi @AllisonKennedy ,

thanks a lot for your help! Unfortunately this DAX Code doesn´t work out for me. It seems that my table still counts meter readers which stopped in the past but in the meantime switched to ok again. I don´t know why because your code makes absolute sense to me. Any ideas?

[IEPMost]

@AllisonKennedy 

I forgot to mention that in my example data are more than one ID, so I have the meter reader for many ID´s. How should I consider that? The measure must count the status "stopped" for every single ID.

Thank you a lot!