Want measures to exclude calculation when date doesn't exist.

[Anonymous]

Completed hold stage for the top three rows hasn't got a completion date populated yet and it's defaulted to 44798.00

All the rows under this dont have a completion date for complaint actionable and it's doing the same thing. 

I want the DAX to give today's date if there is no completion date populated but only if the action EXISTS. I'm trying to get this to show the amount of time for each measure in days. 

Here are the two measures:

Completed Holding stage age =

VAR startdate = MAXX(FILTER('hgmcntac', hgmcntac[action_cd] = "C1001A"), hgmcntac[completed_dt])

VAR enddate = MAXX(FILTER('hgmcntac', hgmcntac[action_cd] = "C1002"), hgmcntac[completed_dt])

RETURN

ROUNDUP((enddate - startdate) * 1,0)

Complaint actionable =

VAR enddate = MAXX(FILTER('hgmcntac', hgmcntac[action_cd] = "C1001A"), hgmcntac[completed_dt])

VAR startdate = MAX(hgmcntct[taken_by_dt])

RETURN

ROUNDUP((enddate - startdate) * 1,0)

Thanks in advance, Dan

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please try

Completed Holding stage age =
VAR startdate =
    MAXX (
        FILTER ( 'hgmcntac', hgmcntac[action_cd] = "C1001A" ),
        hgmcntac[completed_dt]
    )
VAR enddate =
    MAXX (
        FILTER ( 'hgmcntac', hgmcntac[action_cd] = "C1002" ),
        COALESCE ( hgmcntac[completed_dt], TODAY () )
    )
RETURN
    ROUNDUP ( ( enddate - startdate ) * 1, 0 )

[Anonymous]

You're a star. As always.