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'm trying to work out a formula. I've tried IF and SEARCH but no luck.
Here is the fake data I'm playing with:
Project Name | Total inventory Value for project | Inventory Value produced to date | Inventory value depleted BEFORE date | Inventory value depleted AFTER date | Planned Project Completion Date |
Project 1 | 611,871 | 526,368 | 459,846 | 35,944 | 31/10/2016 |
Project 2 | 341,648 | 341,648 | 260,195 | 16,590 | 30/11/2017 |
Project 3 | 2,674,174 | 2,674,174 | 1,898,119 | 776,055 | 30/11/2017 |
Project 4 | 404,521 | 404,521 | 211,298 | - | 15/05/2018 |
Project 5 | 1,263,839 | 1,263,839 | 991,162 | - | 15/05/2018 |
Project 6 | 1,114,729 | 28,014 | 11,832 | - | 15/06/2018 |
Project 7 | 250,506 | 250,506 | 79,421 | - | 30/11/2018 |
Project 8 | 682,131 | 682,131 | 631,972 | 50,158 | 30/11/2017 |
Project 9 | 1,676,731 | 1,676,731 | 737,447 | - | 30/06/2018 |
Project 10 | 5,604,124 | 4,709,595 | 4,622,343 | 83,476 | 30/04/2018 |
I then calculated "Inventory Value Produced left to deplete" as a total using the below:
Inventory Value Produced left to deplete = SUM('Post-Gate 5'[Inventory Value produced to date]) - SUM('Post-Gate 5'[Inventory value depleted BEFORE date]) - SUM('Post-Gate 5'[Inventory value depleted AFTER date])\
And I created CompletionDatePassed? as a calculated column using the below:
CompletionDatePassed? = if(NOW() < ('Post-Gate 5'[Planned Project Completion Date].[Date]),"Project Completion Date in Future", "Project Completion Date in Past")
I'm trying to work out the split between the "Inventory Value Produced left to deplete" column depending on whether the Project Completion Date is in the past or the future (as, naturally, if there is still inventory to deplete after the date then it is a problem!).
Is there a way to incorporate the CompletionDatePassed? criteria into the calculation? Or even the 'Planned Project Completion Date'?
Solved! Go to Solution.
It seems that you may just add FILTER Function.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
It seems that you may just add FILTER Function.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
Yes, that was what I was looking for!
This gave me the result I wanted:
Inventory Value left to Deplete BEFORE Date = CALCULATE(SUM('Post-Gate 5'[Inventory Value produced to date]) - SUM('Post-Gate 5'[Inventory value depleted BEFORE date]) - SUM('Post-Gate 5'[Inventory value depleted AFTER date]), FILTER(CompletionDate, CompletionDate[Completion]="Project Completion Date in Future"))
Thank you 🙂
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |