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.
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 @davidozm ,
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 @davidozm ,
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!
@davidozm , you are welcome!
Proud to be a Super User!
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |