Forum Discussion
Calculate Days Between Dates With Blanks
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!
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.
1 Reply
- Ashish_MathurSuper User
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.