Forum Discussion

Jdokken's avatar
Jdokken
Icon for Helper III rankHelper III
6 years ago

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

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:
 
 

 

 
 
Need Help

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

    Try use this return for your LECA measure:

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

     


    Paul
    Best

    • Jdokken's avatar
      Jdokken
      Icon for Helper III rankHelper III
      6 years ago

      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

       

       

       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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