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