Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |