A measure calculating two different cumulative totals

[IgorM]

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

[Greg_Deckler]

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.

[IgorM]

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]))))