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

[rylach]

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.

[AlB]

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

[rylach]

Hi AIB,

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

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

R.

[AlB · Accepted solution]

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 

[rylach]

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