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

Longest Outstanding Issue -Question which has most recently scored 0 and count of days since first 0

I am currently trying to find the longest outstanding issue in a dataset. I will give you an example of what I am trying to do below:

 

Question                                     Date            Score

Is the dogs leash on?                 10/06/19      90

Has the sink been cleaned?       16/06/19      0

Is there space?                           08/06/19      50

Has the sink been cleaned?       13/05/19      0

Has the sink been cleaned?       26/08/19      100

Is the dogs leash on?                 10/07/19      0

Is the dogs leash on?                 10/08/19      0

Is the dogs leash on?                 10/07/19      0

 

I would like to create a table with the Question text which has been scoring 0 consecutively till today and with a count of number of days which have passed since it scored 0. For example is a question scored 0 consecutively for 5 months, I would like to know which question and how long it has been scoring 0 (basically a count of all the days since it first scored 0). However, if a question scored 0 in 12 jan, 28 jan, march, and then it scored anything above 0 between march and todays date it should be ignored.

 

Basically I am trying to find the longest outstanding issues to date which keep scoring 0

1 REPLY 1
Super User IV
Super User IV

Re: Longest Outstanding Issue -Question which has most recently scored 0 and count of days since fir

You can a table like this using SUMMARIZE and if min and max score =0 then it is getting 0 always

 

 

SUMMARIZE(Questions,Questions[Questions],"MinScore",Min(Questions[Score]),"MaxScore",sum(Questions[Score]),"MinDate",Min(Questions[Date]))

 

 

Also, you can add measure like max(Date), distinctcount(month(date)) and can find occurrence and compare it month diff on min and max date.

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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors