Formula to bring back the most current data and zero for zero/blank

[Jdokken]

I have a formula that almost gives me what I want. My current calculation looks for the last expected contract amount and brings that back for all Year/Months. I'm struggling to get it to bring back a zero if the expected contract amount is zero. When I try to add an IF(SUM(Expected Contract Amt.) >0, 0) it creates a circular reference. 

Here are the two formulas i'm trying:

Here's an example of the data:

 

[V-pazhen-msft]

Hi,

Try use this return for your LECA measure:

Return IF(SUM('IFD&FRR'[Expected Contract Amt])=BLANK(),0,secECA)

Paul
Best

[Jdokken]

Hi Paul, thanks for your response. I've tried that formula, but it gives me the circular dependency error. I've added the calculated column formulas that the circular dependency is referencing. Maybe I have to change something with that so I don't get this error and then I can use the If(SUM(...

I created a column for Cur Ttl Cost Fcst and True % Complete because I couldn't figure out a measure to use for dividing by zero

[V-pazhen-msft]

@Jdokken 

I agree with you that if you sort out error related to the 3 calculated columns, then if(sum will work. If I can have the pbix files, I am sure I can help you out with this troubleshooting. 

Paul

[Jdokken]

The 3 calculated columns I included are what links to the LECA & LGMF.  I need the LECA (Last Expected Contract Amount) and LGMF to calculate the Cur Ttl Cost Fcst.  I need the Cur Ttl Cost Fcst to calculate the True % Complete. And I need the True % Complete  to calculate whether the Contract Value is >.05 or <.95. 

Is there anything else I can provide that would be helpful? I can't provide the PBIX file as the data is confidential. 

[V-pazhen-msft]

@Jdokken 

I really need to know the what are the columns in your tables and the relationship. You can create a sample pbix using random values if possible.

Paul

[Jdokken]

The underlying problem was that I couldn't include a CALCULATE(SUM(... see in blue below, because I have calculated columns that are attached to that data.  The following formula works if I delete the three Calculated columns I change them to measures but I can't get the True % Complete measure to work correctly. I think my best bet would be to recreate the following measure into a calculated column. Would you know how to do that? Otherwise, I can look into creating a sample PBIX. 

LECA = /* LECA is Last Expected Contract Amt. */

//Isolated the Id you want

var myId = calculate(firstNonBlank('IFD&FRR'[Project Number], 'IFD&FRR'[Project Number]))

//Isolates the date you want

var LastReportDate = calculate(max('IFD&FRR'[Date]), Filter(ALL('IFD&FRR'),'IFD&FRR'[Project Number] = myId && 'IFD&FRR'[Expected Contract Amt] >0))

Var TestSum = CALCULATE(SUM('IFD&FRR'[Expected Contract Amt]))

Var lastECA = CALCULATE(SUM('IFD&FRR'[Expected Contract Amt]), Filter(ALL('IFD&FRR'), 'IFD&FRR'[Date]= LastReportDate && 'IFD&FRR'[Project Number] = myId))

Return IF(TestSum = 0 || ISBLANK(TestSum) =TRUE(),0, lastECA)

[Jdokken]

@V-pazhen-msft 

Sorry for the delay. I have a sample PBIX file.  How do I send it to you? As a recap, in the file, it is bringing back the Last ECA and Last GMF, but when the GMF or ECA is zero I need it to bring back zero for that row but the Last entered amount for the other dates. Let me know if you have questions. 

[V-pazhen-msft]

@Jdokken 

You can upload to OneDrive for Business and share the link here. 

Paul Zheng

[Jdokken]

Did you get my private message? @V-pazhen-msft 

[V-pazhen-msft]

@Jdokken 

Sorry I don't check pm. And I don't have the right to access the file, maybe something share setting is limited. Try enable "every one with this link can see" for your file. 

Paul

[Jdokken]

If you have an email address, I could send it there.