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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Kolumam
Post Prodigy
Post Prodigy

Sum a column if date from table 1 lies between start date and end date in table 2

I have two tables. Performance table has start date and end date and Name of the site. Stations table has just date and site name.
I want to sum a column in stations table when date in Stations table lies between start date and end date in performance table
How do I achieve this? Both the tables don't have any relationships now.

 

Kolumam_0-1598864518843.png


Sample Data

Performance Table:

NameStart DateEnd Date
X23/5/202021/10/2021

 

Stations Table

Name Date Value
X23/5/20201
X24/5/20202


So the total value of X should be 3 since Date lies between Start Date and End Date of Performance Table.

@parry2k @mahoney19  @parry2k @az38 @jdbuchanan71 @mahoneypat @edhans @harshnathani @v-kellya-msft @MFelix @Ashish_Mathur @BA_Pete @ryan_mayu @kbuckvol @Alexander76877 @Petazo @Mariusz @TomMartens @Greg_Deckler @tjd @Sean @mikstra @AllisonKennedy @EricHulshof @briandpeterson @USG_Phil @vpatel55 @mwegener @v-piga-msft @tex628 @sturlaws @Vvelarde @CheenuSing @MarcelBeug @Zubair_Muhammad @v-piga-msft @danextian @MattAL @MattAllington @roalexan @Alexander76877 @kgc 

1 ACCEPTED SOLUTION

Thanks Amit! It worked. 

I have one question though.

@amitchandak : One thing I am not clear is that how the above is working even though there is no relationship between them? Is it because of the filter context?

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Kolumam , a new column in the Performance table

 

sumx(filter(Stations, Stations[name]=Performance[name] && Stations[Date]>=Performance[Start Start] && Stations[date]<=Performance[End date]),Stations[Value])

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thanks Amit! It worked. 

I have one question though.

@amitchandak : One thing I am not clear is that how the above is working even though there is no relationship between them? Is it because of the filter context?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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