Perform the calculation only when date is in the past

[dapperscavenger]

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'?

[v-chuncz-msft · Accepted solution]

@dapperscavenger,

It seems that you may just add FILTER Function.

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

[dapperscavenger]

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 🙂