Forum Discussion

zxl1031's avatar
zxl1031
Icon for Helper I rankHelper I
8 years ago
Solved

Calculate dynamic average age by cuttoff date

Hello,   Thanks everyone for taking time to read my post and especially to those who are trying to provide any solution!!   Please find the pbix file here: https://drive.google.com/open?id=1EDgaP...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

     Hi zxl1031,

     

    The return value of "Format" function is text type, so you can't use "Format" function, you should modify your date like pattern 

    yyyy\MM\dd HH:mm:ss

    And then click Modeling-> Date type to change text type to datetime.

     

    Regards,

    Jimmy Tao

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi zxl1031,

 

You should convert column [resolved ] to from text type to date type than use the comparisation condition if your filter. Please check if it can solve your issue.

 

Regards,

Jimmy Tao

  • zxl1031's avatar
    zxl1031
    Icon for Helper I rankHelper I
    8 years ago

    Hi Jimmy,

     

    Yes, I have converted the format of "resolve" column. But not working.

     

    average outstanding age = CALCULATE(AVERAGEX(FILTER(ids,FORMAT(ids[created],"yyyy-mm-dd")<FORMAT(MAX(Dates[date]),"yyyy-mm-dd")&&FORMAT(ids[resolved],"yyyy-mm-yy")>FORMAT(MAX(Dates[date]),"yyyy-mm-dd")),DATEDIFF(ids[created],MAX(Dates[date]),DAY)))

     

    Sorry about the typo in the above measure. It is:

    average outstanding age = CALCULATE(AVERAGEX(FILTER(ids,FORMAT(ids[created],"yyyy-mm-dd")<FORMAT(MAX(Dates[date]),"yyyy-mm-dd")&&FORMAT(ids[resolved],"yyyy-mm-dd")>FORMAT(MAX(Dates[date]),"yyyy-mm-dd")),DATEDIFF(ids[created],MAX(Dates[date]),DAY)))

    • jthomson's avatar
      jthomson
      Icon for Solution Sage rankSolution Sage
      8 years ago

      Year-month-year makes no sense...

      • zxl1031's avatar
        zxl1031
        Icon for Helper I rankHelper I
        8 years ago

        sorry for the typo....

    • v-yuta-msft's avatar
      v-yuta-msft
      Icon for Community Support rankCommunity Support
      8 years ago

       Hi zxl1031,

       

      The return value of "Format" function is text type, so you can't use "Format" function, you should modify your date like pattern 

      yyyy\MM\dd HH:mm:ss

      And then click Modeling-> Date type to change text type to datetime.

       

      Regards,

      Jimmy Tao