Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)))
Solved! Go to 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
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
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)))
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
Year-month-year makes no sense...
sorry for the typo....
How is the user selecting a cutoff date?
Another date table was created for that.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |