Forum Discussion
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
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.
