Forum Discussion

cdizzle22's avatar
cdizzle22
Frequent Visitor
4 years ago
Solved

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

  • 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.