cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Calculate SUM and filter by dates (between different tables)

Hi - I was wondering if someone could help me with this. I got 3 tables: F1, F2 and F3. Each of them got a date column and a value column. I'd like to create a measure that sum all the values in F1 that got the date 01/01/2023 together with all the values in F2 that got the date 01/01/2023 and all the values in F3 with the date 01/01/2023. I tried the below DAX formula, but can't get it right. Would anyone be able to help? 😊

2023 = CALCULATE(SUM(F1[Y1 Value])+SUM(F2[Y2 Value])+SUM(F3[Y3 Value]),FILTER(ALL(F1,F1[TMS Revenue Y1]=DATE(2023, 01, 01), F2[Y2]=DATE(2023, 01, 01), F3[Y3]=DATE(2023, 01, 01))))

Planning to do this for each year up to 2033 and then create a line chart showing the total value for each year that can be filtered by other variables within the tables (it's only the columns above that differ between the 3 tables), so there's a relationship. However, not sure if this would work as the calendar is not aligned between the tables...

Many thanks!
2 REPLIES 2
Super User

@VTork Try:

``````2023 Measure =
VAR __Date = DATE(2023,1,1)
VAR __F1 = SUMX(FILTER(ALL('F1'),[Date] = __Date),[Value])
VAR __F2 = SUMX(FILTER(ALL('F2'),[Date] = __Date),[Value])
VAR __F3 = SUMX(FILTER(ALL('F3'),[Date] = __Date),[Value])
VAR __Result = __F1 + __F2 + __F3
RETURN
__Result``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hi @Greg_Deckler - thank you for getting back to me so quickly!
Sorry I'm still quite new to Power Bi, tried to follow your guidance but didnt get it quite right....

2023 Measure =
VAR __Date = DATE(2023,1,1)
VAR __F1 = SUMX(FILTER(ALL(F1[Y1].[Date] = __Date),F1[Y1 Value]),
VAR __F2 = SUMX(FILTER(ALL(F2[Y2].[Date] = __Date),F2[Y2 Value]),
VAR __F3 = SUMX(FILTER(ALL(F3[Y3].[Date] = __Date),F3[Y3 Value]),
VAR __Result = __F1 + __F2 + __F3
RETURN
__Result ))

Any advice here? 😇

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors