cancel
Showing results for
Did you mean:
Helper II

## Cumulative measure with IF condition inside (greater of two values)

Hi,

I'm looking for a DAX formula (or maybe change in the data model?)  for my cumulative measure. Look at the example, please:

Two tables, A and B, both with two columns - month and Cost (aggregated in Sum A and Sum B measures). Both tables linked to the 'months' table.

Values in particular months are shown in the first two columns.

I want to create a measure, which, starting from the current month, will take greater of the Sum A and Sum B values (and always Sum A for past months).

I tried to do the following:

Historical sum = CALCULATE([Sum A]; FILTER(months;months[month] < DATE(YEAR(TODAY());MONTH(TODAY());1)))

Future Sum A = CALCULATE([Sum A]; FILTER(months; months[month] >= DATE(YEAR(TODAY());MONTH(TODAY());1)))
Future Sum B = CALCULATE([Sum B]; FILTER(months; months[month] >= DATE(YEAR(TODAY());MONTH(TODAY());1)))
Future Sum = IF( [Future Sum A] > [Future Sum B]; [Future Sum A]; [Future Sum B] )

Total Sum = [Historical sum] + [Future Sum]

Cumulative Total Sum = CALCULATE([Total Sum]; FILTER(ALLSELECTED(months);months[month]<=max(months[month])))

Unfortunately, because in Power BI Totals (e.g. in a table visual) are calculated not as sum of rows, but for the current (total) context, it doesn't work correctly. See the following data:

The 'Future Sum' part (1) in 'Total Sum' is taken as 400 (greater of totals of Future Sum A / B) -with 300 from Historical should be 800 and is 700.

And, for the cumulative measure, I'd like to see 800 in the 2020-05 row (2). And here I have no idea where comes here 200 from (diff between 700 and 500).

Do you have any idea how to get such a cumulative measure?

TIA,

Ryszard.

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.
1 ACCEPTED SOLUTION
Super User III

Hi @rylach

Try these:

``````Future Sum V2 =
SUMX (
DISTINCT ( months[month] );
IF ( [Future Sum A] > [Future Sum B]; [Future Sum A]; [Future Sum B] )
)``````

``Total Sum V2 = SUMX(DISTINCT(months[month]); [Historical sum] + [Future Sum])``

``````Cumulative Total Sum V2 =
SUMX (
CALCULATETABLE (
DISTINCT ( months[month] );
FILTER ( ALLSELECTED ( months ); months[month] <= MAX ( months[month] ) )
);
[Total Sum V2]
)``````

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

4 REPLIES 4
Super User III

Hi @rylach

Can you perhaps share the pbix (or a mock version) that reproduces the problem?

It'll be easier to help

Best

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Helper II

Hi AIB,

Yes, that's why I prepared the sample report.

See the report in the shared folder: https://u.unity.pl/3avd

R.

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.
Super User III

Hi @rylach

Try these:

``````Future Sum V2 =
SUMX (
DISTINCT ( months[month] );
IF ( [Future Sum A] > [Future Sum B]; [Future Sum A]; [Future Sum B] )
)``````

``Total Sum V2 = SUMX(DISTINCT(months[month]); [Historical sum] + [Future Sum])``

``````Cumulative Total Sum V2 =
SUMX (
CALCULATETABLE (
DISTINCT ( months[month] );
FILTER ( ALLSELECTED ( months ); months[month] <= MAX ( months[month] ) )
);
[Total Sum V2]
)``````

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Helper II

It works, thank you very much!

In fact, only change of the Future Sum formula fixed all results.

You proposed to change CALCULATE to iterator (in Cumulative measure) as a general good practice, or there are possible cases where the old formula would not work?

Ryszard

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.

Announcements