Forum Discussion
Helper IDateDiff by Date and Status
hello,
I have see an earlier post on the same requirement.
https://community.powerbi.com/t5/Desktop/DateDiff-by-Date-and-Status/m-p/81760
However the formula given there is giving the following error.
I have multiple Status and dates. Any comments?
Hi prabhupan,
Please create a measure using the following formula.measure = VAR max-statu Date = CALCULATE(MAX([StatusDate]),ALLEXCEPT(Table, Table[requestID])) VAR min-start Date = CALCULATE(MIN([StartDate]),FILTER(Table,ALLEXCEPT(Table, Table[requestID])) RETURN DATEDIFF(Table[max-statu Date], Table[min-start Date ],DAY)
Best Regards,
Angelia
5 Replies
- MattAllington
Community Champion
At a minimum you are missing a close bracket for the max in the end1 var
prabhupanIt worked. :)
But not yielding the desired results
I have a table with following items.
I want to find out the date difference between Statu Date and StartDate for each RequestID. The issue here is that the Status had to be "7 Close Pending" and the Statu Date has to be the latest one. (StartDate is same for each RequestID but the Stau Date could be multiple)
v-huizhn-msftMicrosoft Employee
Hi prabhupan，
There are also more close bracket in the ending of FILTER. For your given sample, what's result you wanted, please list the expected result.
Best Regards,
Angelia- prabhupan
hello v-huizhn-msft
I wanted the result as in the "Close Pending Status Column". Refer color codes
For RequestID ending 028, Max(Stau Date) = 01-03-2017 and Start Date = 01-01-2017.
"Close Pending Status Column" = Max(Stau Date) - Start Date where Status = "7 Close Pending". (60 is the answer in this case)
- v-huizhn-msft
Hi prabhupan,
Please create a measure using the following formula.measure = VAR max-statu Date = CALCULATE(MAX([StatusDate]),ALLEXCEPT(Table, Table[requestID])) VAR min-start Date = CALCULATE(MIN([StartDate]),FILTER(Table,ALLEXCEPT(Table, Table[requestID])) RETURN DATEDIFF(Table[max-statu Date], Table[min-start Date ],DAY)
Best Regards,
Angelia
