Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Cumulative measure with IF condition inside (g...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

04-20-2020
01:49 PM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-21-2020
04:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-21-2020
03:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-21-2020
04:01 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-21-2020
04:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-21-2020
04:51 AM

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

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