Forum Discussion

prabhupan's avatar
prabhupan
Icon for Helper I rankHelper I
9 years ago
Solved

DateDiff 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 fo...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
9 years ago

At a minimum you are missing a close bracket for the max in the end1 var

prabhupan's avatar
prabhupan
Icon for Helper I rankHelper I
9 years ago

MattAllington,

 

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