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.
hackfifi's avatar
hackfifi
Icon for Helper V rankHelper V
9 years ago

Tried using the below (25th June 2016)

Var LNBD = date(2017,06,25)

 

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
9 years ago
Ok so to me then it is working as expected because the last date was 25 June 2016, so the line is going as far as that last date?
  • hackfifi's avatar
    hackfifi
    Icon for Helper V rankHelper V
    9 years ago

    Yes, the line has always been working perfectly.

    The line "value" is incorrect i.e. it not calculating as cumulative.

    The line "value" is as COLUMN D below, but i need the values to be as COLUMN E (cumulative)

     

     

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago
    Ok I can see that thanks. What does the Period column look like?

    And when that is compared to the measure, do the Periods match up to the LNBD?
  • hackfifi's avatar
    hackfifi
    Icon for Helper V rankHelper V
    9 years ago

    Thanks again GilbertQ

     

    Hopefully i understood ur last query correctly. The LNBD works perfectly, as you can see from the below data --> the LNBD is 25/06/2017. The value of Actual after this date is NULL. Hence, i need the cumulative sum of the NON-NULL values

     

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago
    Ok thanks for that, when you put the above into a table, but this time using the measures for the [Progress % Actual], as well as the Period, and then the ]Cumulatve Actual]?

    The only thing that I have always done is to have a Date table and I use the Date table for my cumulative totals to ensure that I always have the dates, even when there is no data.
  • hackfifi's avatar
    hackfifi
    Icon for Helper V rankHelper V
    9 years ago

     

    GilbertQ - Thanks again for your help. I just cant work out why the "Cumulative Actual" Formula doesnt work.

    Cumulative Plan & Cumulative Forecast works perfectly. 

     

    The formulas used are:

     

    Cumulative Plan =
    CALCULATE (
    'F2 Hours'[Progress % F2 Plan],
    FILTER (
    ALLSELECTED('Progress Curve'),
    ('Progress Curve'[Period] <= MAX ( 'Progress Curve'[Period]))))

     

    Cumulative Forecast =
    CALCULATE (
    'F2 Hours'[Progress % Forecast],
    FILTER (
    ALLSELECTED('Progress Curve'),
    ('Progress Curve'[Period] <= MAX ( 'Progress Curve'[Period]))))

     

    Cumulative Actual =
    CALCULATE(
    'F2 Hours'[Progress % Actual],
    FILTER(
    ALL('Progress Curve'[Period]),
    'Progress Curve'[Period] <= [Var LNBD]))

     

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    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.
  • hackfifi's avatar
    hackfifi
    Icon for Helper V rankHelper V
    9 years ago

    Sorry GilbertQ - i took a while to get back - stil no JOY.

    Year & Month based on "Progress Curve" Table: BLACK LINE

    Year & Month based on "Date" Table: RED LINE