Forum Discussion

hackfifi's avatar
hackfifi
Icon for Helper V rankHelper V
9 years ago
Solved

Flat Line in Cumulative Curve

Hey Guys - Any idea how to get rid of the BLACK FLAT LINE for Actuals? The Actual Curve should stop after TODAY's Date. The data extract is also shown below.   The Formula used for the Actual Curve...
Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    Hi hackfifi

     

    What I would suggest you do is the following:

     

    Create a measure for your A Value with the example below: 

    A Total = IF(SUM('Table2'[A]) = 0,BLANK(),SUM('Table2'[A]))

     The above will always ensure that there is one place to change it, as well as then make it easier for the filter context later.

     

    Then with the above measure you can now create your cumulative curve with the following measure.

     

    Cumulative Actual = 
Var LNBD = LASTNONBLANK('Table2'[Date],[A Total])
RETURN
CALCULATE([A Total],FILTER(ALL('Table2'[Date]), 'Table2'[Date] <= LNBD))
  • GilbertQ's avatar
    GilbertQ
    9 years ago
    Hi there for your Year and Month values are those coming from the 'Progress Curve' table or the date table?

    I think that might be the reason why it is not working as expected.
GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
9 years ago

Hi hackfifi

 

Ok that is because the values are not NULL, but have a zero

 

This should then work, what I am doing is saying if the value is 0 then make it BLANK, which will then create the right filter context for the LASTNONBLANK

 

CALCULATE (
    IF(SUM ( 'Contract Closeout Combined'[A] ) =0,BLANK(),SUM ( 'Contract Closeout Combined'[A] )),
    FILTER (
        'Contract Closeout Combined' ,        
            'Contract Closeout Combined'[Date]
                <= LASTNONBLANK (
                    'Contract Closeout Combined'[Date],
                    SUM ( 'Contract Closeout Combined'[A] )
                )
        )    
)
    / SUM ( 'Total Progress (Forecast)'[Total Progress (Forecast)] )
hackfifi's avatar
hackfifi
Icon for Helper V rankHelper V
9 years ago

Hi GilbertQ - I think we (you actually) are almost there. The Curve stops on the final data date, but the only issue now is that it is NOT calculating "cumulative"

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi hackfifi

     

    What I would suggest you do is the following:

     

    Create a measure for your A Value with the example below: 

    A Total = IF(SUM('Table2'[A]) = 0,BLANK(),SUM('Table2'[A]))

     The above will always ensure that there is one place to change it, as well as then make it easier for the filter context later.

     

    Then with the above measure you can now create your cumulative curve with the following measure.

     

    Cumulative Actual = 
Var LNBD = LASTNONBLANK('Table2'[Date],[A Total])
RETURN
CALCULATE([A Total],FILTER(ALL('Table2'[Date]), 'Table2'[Date] <= LNBD))
    • hackfifi's avatar
      hackfifi
      Icon for Helper V rankHelper V
      9 years ago

      Thanks GilbertQ.  I will give it a shot

       

      1. Is 'Table2' a new TABLE with "A" column only or do i refer to my existing table 'Contract Closeout Combined'?

      2. I have not tried it yet, but is it the correct syntax to have "=" twice?

      Cumulative Actual = 
Var LNBD = LASTNONBLANK('Table2'[Date],[A Total])
RETURN
CALCULATE([A Total],FILTER(ALL('Table2'[Date]), 'Table2'[Date] <= LNBD))

       

      • GilbertQ's avatar
        GilbertQ
        Icon for Super User rankSuper User
        9 years ago

        Hi hackfifi

         

        Yes replace 'Table2' with your table name.

         

        Yes the first one is a VARIABLE denoted as Var, so it will work as expected.