Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
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]))))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |