PowerBI: Aggregate Measure correctly by condition on DATEDIFF

user_15062253 0
Viewed 419

I have the following Table:

BaseTable

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]),DATUM[Date]<=MAX(DATUM[Date]),ALL(DATUM)))

or

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

The Measures AGE, OLD and NEW look like that with the Base Table:

Measures

As you can see the aggregation is not working correctly:

Result_Wrong

But it should be like that

Result_Correct

Any idea how to fix that?

Thank you!

dax powerbi aggregation datediff conditional-statements
2 Answers

So the problem is that the subtotal is calculated at a whole different context, and because your Age measure is based on the MAX(Workflow[MIN_TIMESTAMP]) that won't take into account that there can be multiple processes.

To do what you want, you need to change the New and Old measures to perform an aggregation per process and then return the result of that. Something like this:

New_agg = 
VAR tbl = ADDCOLUMNS(CALCULATETABLE(VALUES(Workflow[Process]), ALL('Date')), "age", [Age], "count_process", CALCULATE(COUNT(Workflow[Process]), ALL('Date')))
RETURN SUMX(tbl, IF([age]<=3, [count_process]))

Demo File

user_13090270

Let me know if below solution is working

Unfortunately I am unable to generate the dummy data that you have been using, so Created my own data for developing the solution.

enter image description here

Now from this data I have calculated the difference of dates and put it as Age

enter image description here

Now to get the count of process for the condition like yours, I have created two formulas and the result is:

Logic I followed here is, instead of creating measure I have created columns and took the sum of those columns which will give the data you need as sum of those columns.

Column for New:

New = IF((Sheet1[Age]) > 20, 1,0)

Column for Old:

Old = IF((Sheet1[Age]) < 20, 1,0)

Now place both formulas in "Values" and take sum as the aggregation.

Final result is

enter image description here

user_22935600
Related