cancel
Showing results for
Did you mean: Regular Visitor

## A measure calculating two different cumulative totals

Hi,

I need a measure that will calculate two different cumulative totals depanding on one of the column values.

The below table contains a sample data set. The Date column is connected to a Calendar table in my model. For the following set I need to write a measure that will:

1) for Country=Poland calculate YTD cumulative total of Amount

2) for Country=UK calculate lifetime to date total of Amount. The following measure correctly calculates such a lifetime to date total but I don't know how to combine it in a single measure with the condition from point 1:  CALCULATE(SUM[Amount];FILTER(ALL('Calendar'[Date]);'Calendar'[Date]<=MAX('Calendar'[Date])))

 Country City Date Amount Poland Gdansk 09.06.2018 458,14 Poland Gdansk 01.01.2019 354,02 Poland Krakow 11.06.2018 678,38 Poland Krakow 30.12.2018 775,82 UK London 02.09.2018 995,06 UK London 03.02.2019 342,28 UK London 09.05.2019 528,65 UK Cambridge 16.06.2018 499,19
2 REPLIES 2  Super User IV

Should be something like:

```Measure =
VAR __country = MAX([Country])
RETURN
SWITCH(__country,
"Poland",[Some measure],
"UK",CALCULATE(SUM[Amount];FILTER(ALL('Calendar'[Date]);'Calendar'[Date]<=MAX('Calendar'[Date]))),
BLANK()
)```

You could also use an IF statement instead of SWITCH but SWITCH is usually better.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User! Regular Visitor

Hi Greg,

no I am afraid it is not working the way I wanted it to work. Below is a complete measure code. I am testing it in Power Pivot that's why there is IF instead of SWITCH. It seems to me that the IF test always fails and therefore the false part of the if statement is always executed.

If the code was correct 2 should be returned for Gdansk, since there is only one entry for Gdansk with the date in 2019 (second row of the source table) - please notice that I have changed the amounts in the sample table for simplicity reasons. ```=VAR __country = MAX(SourceData[Country])
RETURN
IF(__country="Poland";TOTALYTD(SUM(SourceData[Amount]);'Calendar'[Date]);
CALCULATE(SUM(SourceData[Amount]);FILTER(ALL('Calendar'[Date]);'Calendar'[Date]<=MAX('Calendar'[Date]))))```    