Forum Discussion

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

zxl1031

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

v-yuta-msft

Community 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