cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rylach
Helper II
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:

 

2020-04-20 22_42_08-Untitled - Power BI Desktop.png

 

 

 

 

 

 

 

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
AlB
Super User III
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 

 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
AlB
Super User III
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

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.
AlB
Super User III
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 

 

SU18_powerbi_badge

View solution in original post

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors