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=1EDgaPSgfhTQaQBZcWkIAkJfP5pMS0njz

 

I want to calculate the dynamic average age of outstanding issues based on cutt off date.

outstanding issues are those issues whoese resolved date is after cutt off date.

issue age = [cutt off date] -[created]

 

 

 

The result table is expected to be like this:

 

Below measure is 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)))

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

7 Replies

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

      Another date table was created for that. 

  • 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...