# DAX - measure with a cumulative sum and a condition

• ### Question

• Hi,

I want to write a measure that would accumulate all figures to date but only if the country (one of the columns) is UK; otherwise blank should be returned.

The following table contains the source data. I also have a Calendar table linked to this one.

I managed to write a measure that correctly calculates a cumulative to date sum but it sums the numbers up for all countries and I'd like to add a condition to this measure that would only calculate the cumulative sum if Country=UK.

=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

-- Igor M.

• Edited by Monday, August 5, 2019 6:38 PM
Monday, August 5, 2019 6:37 PM

### All replies

• Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Cumulative data for each country.
Selectable with Slicer.
http://www.mediafire.com/file/g1pf2ji8l8q8oxn/08_05_19.xlsx/file
http://www.mediafire.com/file/99ltdotj8pjzws1/08_05_19.pdf/file

Monday, August 5, 2019 10:33 PM
• Hi Herbert,

I'm sorry but your solution does not do what I wanted. The measure is calculated for every country in the data set. And I wanted the measure to be calculated for only one specific country. For all other countries the measure should have returned no values. This was my initial request.

I need to clarify it now.  Apologies for the confusion. I actually want the measure to calculate YTD cumulative sum if the country is X (say Poland) and lifetime to date sum if the country is Y (say UK).

Kind regards,

-- Igor M.

Tuesday, August 6, 2019 8:08 AM
• Pass
Tuesday, August 6, 2019 2:37 PM