cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IgorM
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])))

 

CountryCityDateAmount
PolandGdansk09.06.2018458,14
PolandGdansk01.01.2019354,02
PolandKrakow11.06.2018678,38
PolandKrakow30.12.2018775,82
UKLondon02.09.2018995,06
UKLondon03.02.2019342,28
UKLondon09.05.2019528,65
UKCambridge16.06.2018499,19
2 REPLIES 2
Greg_Deckler
Super User IV
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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

Capture.PNG

 

 

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

 

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 Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors