Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello everyone,
This is my first time using the forum so hopefully I have disobeyed any rules, apologises in advance.
I am trying to sum an amount from one table based upon a date from another table. The amount can't be totalled if the production date is in the past of the install date.
Here is my first Table.
Production Table
Production date | Amount |
2/1/2017 | 5 |
2/1/2017 | 10 |
2/1/2018 | 50 |
2/1/2019 | 500 |
here is my second table
Asset ID | Install Date |
asset 1 | 1/1/2017 |
asset 2 | 1/1/2018 |
asset 3 | 1/1/2019 |
asset 4 | 1/1/2020 |
asset 5 | 1/1/2017 |
So I would expect my results as this.
Asset ID | Install Date | What I want |
asset 1 | 1/1/2017 | 565 |
asset 2 | 1/1/2018 | 550 |
asset 3 | 1/1/2019 | 500 |
asset 4 | 1/1/2020 | 0 |
asset 5 | 1/1/2017 | 565 |
I have a slicer table that has a unique date range.
I was trying = calculate (sum(table1[amount])), filter(allselected(table1), production date > userrelationship(production date, install date)), but that doesn't seem to work.
Any ideas would be very appreciated.
Thanks in advance
Solved! Go to Solution.
Hi @Anonymous ,
Try this :
Sum of Amounts = SUMX(Production,IF(Production[Production date]>MAX(mySecond[Install Date]),Production[Amount],0))
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi @Anonymous ,
Try this :
Sum of Amounts = SUMX(Production,IF(Production[Production date]>MAX(mySecond[Install Date]),Production[Amount],0))
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
@Anonymous , you are welcome!
Proud to be a Super User!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |