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

Question

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 Answer

Hi&nbsp;Anonymous&nbsp;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 )

Forum Discussion

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

2 Replies

Related content

DAX Calculated Measure

Calculating Average excluding MEASURE data

DAX Measure for Ranking the valid rows and calculating the average rating

Exclude blank data from calculations

Measure or Calculated Column