cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Stampers Frequent Visitor
Frequent Visitor

Count of days since last value >0

I am very new to Power Bi and I'm creating a dashboard to assist in our Health and Safety statistics.

I would like to create a new measure for each of the columns in our data table that displays the number of days since last the incident, per heading.

 

For example, number of days since last "Concern" would be the first one to do, and then continue to create one for each column heading (Near Miss, PPE violation, etc).

 

The measure would need to look at the "Concern" column, find the last value that isn't 0, take note of the date that this occurred and count the number of days from then until the current date and provide the value. Or alternatively, look through the "Concern" column from today's date backwards, counting the number of 0 values until it reaches a number that isn't 0.

In this instance, the value returned would be 2, and for "Near Miss" it would be 5, and so on.

 

Is this possible?

 

I can do this in SQL (completely unrelated) but I'm only just starting out with DAX.

I have included a screen-grab of the current table to show the structure.

 

Any help is greatly appreciated!

 

pbihstable.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
fhill Senior Member
Senior Member

Re: Count of days since last value >0

...  Since you aren't populating data for the holidays, I'll use your raw data date values as the COUNT logic isntead of trying to do some complicated calendar options.  Yes, i'm kinda cheating, but see if these work.

 

 

Days Since 0 Concern = CALCULATE(DISTINCTCOUNT('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Date] >= CALCULATE(MAX('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Attribute] = "Concern" && 'Table1 (2)'[Value] = 0))))

 

Days Since 0 NearMiss = CALCULATE(DISTINCTCOUNT('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Date] >= CALCULATE(MAX('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Attribute] = "NearMiss" && [Value] = 0))))

View solution in original post

3 REPLIES 3
fhill Senior Member
Senior Member

Re: Count of days since last value >0

 

Since you are getting this data from SQL, it's likely the values are stored in a more Database friendly format as shows in the 2nd table below.  If not, you can use PowerBI's 'UnPivot Tables' feature to turn your Landscape data into a more Portrait format:

 

Once you split the data up, it's easy to use some FILTERs with MAX to find the last Zero date.  Naturally you can combine these DAX coes into one DAX, but I split them to to help step you thru the code.

 

Last 0 Concern = CALCULATE(MAX('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Attribute] = "Concern" && 'Table1 (2)'[Value] = 0))

Last 0 NearMiss = CALCULATE(MAX('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Attribute] = "NearMiss" && [Value] = 0))

Days Since 0 Concern = DATEDIFF([Last 0 Concern],TODAY(),DAY)

Days Since 0 NearMiss = DATEDIFF([Last 0 NearMiss],TODAY(),DAY)

 

 

Last zero.PNG

Stampers Frequent Visitor
Frequent Visitor

Re: Count of days since last value >0

That's brilliant, thank you so much.

 

It's working perfectly for the "Concern" column now providing a value of 2, however it gives me the value of 7 for the "Near Miss" column as opposed to the expected value of 5. I believe this may be due to it accounting for the weekend days as part of the calculation.

 

Would there be any way to adjust the DAX to discount the weekend days?

The raw data itself does not have the dates for the weekends and national holidays and only contains the data for the working days of the month (as these are the only ones that we log). I believe it would need to be incorporated into the "Days Since 0 NearMiss" measure as the "Last 0 NearMiss" measure is providing the correct date itself.

fhill Senior Member
Senior Member

Re: Count of days since last value >0

...  Since you aren't populating data for the holidays, I'll use your raw data date values as the COUNT logic isntead of trying to do some complicated calendar options.  Yes, i'm kinda cheating, but see if these work.

 

 

Days Since 0 Concern = CALCULATE(DISTINCTCOUNT('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Date] >= CALCULATE(MAX('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Attribute] = "Concern" && 'Table1 (2)'[Value] = 0))))

 

Days Since 0 NearMiss = CALCULATE(DISTINCTCOUNT('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Date] >= CALCULATE(MAX('Table1 (2)'[Date]), FILTER('Table1 (2)', 'Table1 (2)'[Attribute] = "NearMiss" && [Value] = 0))))

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors