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
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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
135 | |
112 | |
98 | |
98 | |
92 |