Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
102 | |
77 | |
77 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |