Aggregate Measure correctly by condition on DATEDIFF

[Lourini90]

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)))

or

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!

[v-yingjl]

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.

[AlexisOlson]

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] )

[Lourini90]

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

any idea? 🙂

[AlexisOlson · Accepted solution]

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.