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

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
prabhupan
Icon for Helper I rankHelper I
9 years ago

 

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's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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