topic Re: Cumulative measure with IF condition inside (greater of two values) in DAX Commands and Tips

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

rylach — Mon, 20 Apr 2020 20:49:40 GMT

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.

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

AlB — Tue, 21 Apr 2020 10:39:01 GMT

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

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

rylach — Tue, 21 Apr 2020 11:01:21 GMT

Hi AIB,

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

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

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

AlB — Tue, 21 Apr 2020 11:40:35 GMT

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

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

rylach — Tue, 21 Apr 2020 11:51:44 GMT

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