Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Sample Data
Performance Table:
Name | Start Date | End Date |
X | 23/5/2020 | 21/10/2021 |
Stations Table
Name | Date | Value |
X | 23/5/2020 | 1 |
X | 24/5/2020 | 2 |
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
Solved! Go to 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?
@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])
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |