Calculate dynamic average age by cuttoff date

[zxl1031]

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

[v-yuta-msft]

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]

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

[v-yuta-msft · Accepted solution]

 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

[jthomson]

Year-month-year makes no sense...

[zxl1031]

sorry for the typo....

[jthomson]

How is the user selecting a cutoff date?

[zxl1031]

Another date table was created for that.