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.
Good Afternoon,
I am trying to calculate the number of days between dates using the below measure:
Days to Resolve (Measure) = SUMX(incident,DATEDIFF('incident'[sys_created_on],incident[resolved_at],DAY))
This works fine but am running into issues when it comes to resolved_at having blank values. I want to add in logic that if the resolved_at field is blank, it replaces it with today's date "today()" and then color codes it to show that it has not been resolved.
I found the below expressions that looks like it takes care of the blank issue if I create it as a column:
Column = IF(ISBLANK(Table[End Date]),DATEDIFF(Table[Start Date],TODAY(),DAY),DATEDIFF(Table[Start Date],Table[End Date],DAY))
(https://community.powerbi.com/t5/Desktop/DATEDIFF-with-blank-dates/m-p/202290)
This column works just fine but ideally I would like to create this as a measure to make it more efficient. When I use the expression in a measure it only allows me to select other measures at the "ISBLANK" point.
I believe there is a simple concept I am missing here.
Why is it that when I create this as a column I can reference other columns but when I create it as a measure it will only let me use other measures?
I am looking into the color coding stuff now and believe I can figure that part out on my own but if anyone has any advice it is appreciated. Thanks!
Solved! Go to Solution.
Hi,
In the incident table, write this calculated column formula
Revised resolved date = if(isblank('incident'[resolved date]),today(),'incident'[resolved date])
In your measure, replace resolved date with Revised resolved date.
Hope this helps.
Hi,
In the incident table, write this calculated column formula
Revised resolved date = if(isblank('incident'[resolved date]),today(),'incident'[resolved date])
In your measure, replace resolved date with Revised resolved date.
Hope this helps.
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |