Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pikachu-Power
Post Prodigy
Post Prodigy

cumulate over year

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?
1 ACCEPTED SOLUTION
Pikachu-Power
Post Prodigy
Post Prodigy

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.

View solution in original post

2 REPLIES 2
Pikachu-Power
Post Prodigy
Post Prodigy

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
Super User
Super User

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.