Filter Measures for a particular year

Question

I´m new to DAX and have a question regarding filtering measures. As you can see in the attachment, I want to filter both measures, volume CY (current year) and volume LY only for year 2018.

        Volume LY   Volume CY
2016    65.024.635
2017    65.024.635  63.602.450
2018    63.602.450  21.597.407

I want to link the filtering with an indicator (Counter). This counter has values in range [0-3]. Each year has semesters, for example Current Year has semester 0 and 1, 2017 - 2 and 3, 2016 - 4 and 5. In this case, I want to select 1 for the counter and it should filter semester <= 1 for the year 2018:

        Volume LY   Volume CY
2018    63.602.450  21.597.407

But if I filter measure volume CY with:

FILTER(ALLSELECTED(Table);Table[Semester]<=MAX('Table2'[Counter]));

then measure Volume LY automatically gets filtered for semester <= 1 and it won´t show values for LY. My questions is: How can i use filter function to get values for both measures for current year?

Measure Volume Current Year:

Volume_CY:=CALCULATE(
[Volume];
'Scenario'[Scenario1]=1;
FILTER(Table;Table[Semester]<=MAX('Table2'[Counter])))

Measure Volume LY:

  Volume LY:= CALCULATE(
    [Volume_CY];
    FILTER(ALLSELECTED(Table);Table[Semester]<=MAX('Table2'[Counter]));
    FILTER(ALL(Table[Year]))

Here is the result of this two measures with counter 1 and scenario 1: As you can see it doesn´t show the LY Value which should be 63.602.450.

Scenario    1
Counter 1   

        Volume LY   Volume CY
2018               21.597.407
Total              21.597.407

Now i have a result like this:

        Volume CY    Volume LY
20151   77.222.12   77.222.12 
20152   88.868.719  88.868.719
20161   87.987.22   87.987.22
20162   92.906.793  92.906.793
20171   101.102.12  101.102.12
20172   105.029.725 105.029.725

And this is what i´m expecting:

        Volume CY    Volume LY
20151   77.222.12
20152   88.868.719  49.123.12
20161   87.987.22   77.222.12
20162   92.906.793  88.868.719
20171   101.102.12  87.987.22
20172   105.029.725 92.906.793

How about this? Volume_LY:=CALCULATE( [Volume]; 'Scenario'[Scenario1]=1; FILTER(Table;Table[Semester]<=MAX('Table2'[Counter])-2)) — user5226582

user5226582 user5226582 · Accepted Answer · 2017-04-12T06:53:23

Edit:

Given following source data:

year,   Volume CY
20151,  77222.12
20152,  88868.719
20161,  87987.22
20162,  92906.793
20171,  101102.12
20172,  105029.725

Expected result could be achieved with a following calculated column:

Volume LY = CALCULATE(SUM(Table[Volume CY]),
  ALL(Table1),
  Table[year] = EARLIER(Table[year])-10)

Which results in:

year,   Volume CY,  Volume LY
20151,  77222.12,
20152,  88868.719,
20161,  87987.22,   77222.12
20162,  92906.793,  88868.719
20171,  101102.12,  87987.22
20172,  105029.725, 92906.793

Which respects filters on "year" column.

(Old answer below)

Rolling sum:

year    semester    volume
2016    1           123
2017    3           1345
2018    5           3465
2016    2           3465
2017    4           3465
2018    6           634

Calculated columns:

VCY = CALCULATE(SUM(Table1[volume]),
  ALL(Table1),
  Table1[semester] <= EARLIER(Table1[semester]))

VLY = CALCULATE(SUM(Table1[volume]),
  ALL(Table1),
  Table1[semester] <= EARLIER(Table1[semester]) - 2)

Results in:

year, semester, volume, VCY,   VLY
2016, 1,        123,    123,
2016, 2,        3465,   3588,
2017, 3,        1345,   4933,  123
2017, 4,        3465,   8398,  3588
2018, 5,        3465,   11863, 4933
2018, 6,        634,    12497, 8398

Rolling sum on yearly basis:

VCY = 
VAR RowYear = Table1[year]
RETURN CALCULATE(SUM(Table1[volume]),
  ALL(Table1),
  table1[year] = RowYear,
  Table1[semester] <= EARLIER(Table1[semester]))

VLY = 
VAR RowYear = Table1[year]-1
RETURN
CALCULATE(SUM(Table1[volume]),
  ALL(Table1),
  table1[year] = RowYear,
  Table1[semester] <= EARLIER(Table1[semester])-2)

Results in:

year, semester, volume, VCY,  VLY
2016, 1,        123,    123,
2016, 2,        3465,   3588,
2017, 3,        1345,   1345, 123
2017, 4,        3465,   4810, 3588
2018, 5,        3465,   3465, 1345
2018, 6,        634,    4099, 4810

Filter Measures for a particular year

2 Answers