Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Stampers
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
fhill
Resident Rockstar
Resident Rockstar

...  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))))




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




View solution in original post

3 REPLIES 3
fhill
Resident Rockstar
Resident Rockstar

 

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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
Resident Rockstar
Resident Rockstar

...  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))))




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.