topic Re: Want measures to exclude calculation when date doesn't exist. in DAX Commands and Tips

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

Anonymous — Tue, 30 Aug 2022 10:27:13 GMT

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

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

tamerj1 — Tue, 30 Aug 2022 10:42:53 GMT

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 )

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

Anonymous — Wed, 31 Aug 2022 09:46:58 GMT

You're a star. As always.