topic Re: Aggregate Measure correctly by condition on DATEDIFF in DAX Commands and Tips

Aggregate Measure correctly by condition on DATEDIFF

Lourini90 — Mon, 18 Oct 2021 16:04:47 GMT

I have the following Table:

It represents processes with a certain category.

And there is also a Date Table over column TIMESTAMP.

I would like to show a Measure based on another Measure that calculates the Date-Difference until the selected Date.

So first this is how I calculate the Date-Difference:

AGE = 
VAR SELECTED_DATE = CALCULATE(MAX(DATUM[Date]), ALLSELECTED(DATUM))
VAR STARTDATE_PROCESS = Calculate(MAX(Workflow[MIN_TIMESTAMP]),DATUM[Date]<=MAX(DATUM[Date]), ALL(DATUM[Date]))
RETURN
DATEDIFF(STARTDATE_PROCESS,SELECTED_DATE,DAY)

Now I want to use a Measure which depends on the result of AGE, like

NEW = IF([AGE]<=3,CALCULATE(COUNT(Workflow[PROCESS]),ALL(DATUM)))

OLD = IF([AGE]>3,CALCULATE(COUNT(Workflow[PROCESS]),ALL(DATUM)))

That works so far, but the aggregation is not working correctly as you can see here, there should be a 3 on NEW and OLD aggregated:

Any idea how to fix that?

Thank you!

Re: Aggregate Measure correctly by condition on DATEDIFF

AlexisOlson — Mon, 18 Oct 2021 16:24:00 GMT

You need to sum over each process individually or else the maximums in AGE are taken over all of the processes simultaneously rather than one at a time.

SumNew = SUMX ( VALUES ( Workflow[PROCESS] ), [New] )

Re: Aggregate Measure correctly by condition on DATEDIFF

Lourini90 — Mon, 18 Oct 2021 17:56:50 GMT

Thanks for replying!
For some reason nothing is shown here:

any idea? 🙂

Re: Aggregate Measure correctly by condition on DATEDIFF

AlexisOlson — Mon, 18 Oct 2021 21:33:44 GMT

Hmm. I think ALLSELECTED ( DATUM ) is destroying the PROCESS filter context. Try ALLSELECTED ( DATUM[Date] ) instead.

I'm not sure that this will work though. There's a decent chance you'll have auto-exists trouble without a date dimension table.

Re: Aggregate Measure correctly by condition on DATEDIFF

v-yingjl — Thu, 21 Oct 2021 06:12:19 GMT

Hi Lourini90 ,

You can try to modify the measures like this:

NEW = IF ( HASONEVALUE ( 'Workflow'[PROCESS] ), IF ( [AGE] <= 3, CALCULATE ( COUNT ( 'Workflow'[PROCESS] ), ALL ( DATUM ) ) ), CALCULATE ( COUNT ( Workflow[PROCESS] ), FILTER ( 'Workflow', [AGE] <= 3 ) ) )

OLD = IF ( HASONEVALUE ( 'Workflow'[PROCESS] ), IF ( [AGE] > 3, CALCULATE ( COUNT ( 'Workflow'[PROCESS] ), ALL ( DATUM ) ) ), CALCULATE ( COUNT ( Workflow[PROCESS] ), FILTER ( 'Workflow', [AGE] > 3 ) ) )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.