cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IEPMost
Helper III
Helper III

Meter reader stopped counting - calculate for how many days!

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:

 

DateIDmeter readerstatus
01.01.20203200ok
02.01.20203230ok
03.01.20203230stopped
04.01.20203230stopped
05.01.20203240ok
06.01.20203240stopped
07.01.20203240stopped
08.01.20203240stopped

 

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!!

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

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)

 

me1.jpg

 

Then we can put the column in a card visual.

 

me2.jpg

 

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.

View solution in original post

7 REPLIES 7
IEPMost
Helper III
Helper III

Hi @v-zhenbw-msft ,

 

thank you a lot, it works out perfectly now!

v-zhenbw-msft
Community Support
Community Support

Hi @IEPMost ,

 

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

 

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
Community Support
Community Support

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)

 

me1.jpg

 

Then we can put the column in a card visual.

 

me2.jpg

 

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.

View solution in original post

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
Super User III
Super User III

@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



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

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?

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors