cancel
Showing results for
Search instead 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.  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group! #### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks Top Solution Authors
Top Kudoed Authors
Users online (5,246)