cumulate over year

[Pikachu-Power]

hi all,

i use following formula to cumulate over years:

DB_cumulate_year = CALCULATE(SUM(Table1[DB]), FILTER(ALL(Table1), Table1[Year] <= MAX(Table1[Year])))

The problem is that i also use a Table2 which is connected with Table1. On my canvas I have three slicer with columns from Table2. That dont work because I use ALL(Table1).

How could I avoid ALL(Table1) and still cumulate over years?

[Pikachu-Power · Accepted solution]

Thank you! But i found a third way... just use ALLEXEPT with the exeption of the three slicer from Table2. Seems to work 🙂

1 - Many is right but from Table 2 to Table 1.

[amitchandak]

@Pikachu-Power , if that 1 - Many from Table 1 to table 2 and you are using year from table 1 it should work.

Else create a common year table and use that (join with both tables)

CALCULATE(SUM(Table1[DB]), FILTER(ALL(Year), Year[Year] <= MAX(Year[Year])))

CALCULATE(SUM(Table2[DB2]), FILTER(ALL(Year), Year[Year] <= MAX(Year[Year])))