cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

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 is:

Cumulative Actual = CALCULATE (

    SUM ('Contract Closeout Combined'[A]),

    FILTER (

        ALLSELECTED('Contract Closeout Combined'),

        ('Contract Closeout Combined'[Date] <= max('Contract Closeout Combined'[Date]))

))/sum('Total Progress (Forecast)'[Total Progress (Forecast)])

 

i tried replacing "('Contract Closeout Combined'[Date] <= max('Contract Closeout Combined'[Date]) with Today()"

but that did not work. 

 

Data.PNGFlatline.PNG

2 ACCEPTED SOLUTIONS

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))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

24 REPLIES 24
Super User II
Super User II

Hi @hackfifi


This should work below, might need one round bracket

 

Cumulative Actual =
CALCULATE (
    SUM ( 'Contract Closeout Combined'[A] ),
    FILTER (
        ALLSELECTED ( 'Contract Closeout Combined' ),
        (
            'Contract Closeout Combined'[Date]
                <= LASTNONBLANK (
                    'Contract Closeout Combined'[Date],
                    SUM ( 'Contract Closeout Combined'[A] )
                )
        )
    )
)
    / SUM ( 'Total Progress (Forecast)'[Total Progress (Forecast)] )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @hackfifi

 

Apologies Monday morning try this instead

 

CALCULATE (
    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)] )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thanks again @GilbertQ! - but no luck! 🙂

I have attached updated curve and data snippet of that point. Do you think it has anything to do with null/0 values?

Also you will see anything afte 11th June 2017 is 0, as it is TODAY'S date. (data snippet also attached)

 

Flatline-3.PNGData2.PNGData3.PNG

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)] )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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"

Flatline-4.PNG

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))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

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))

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Mate - It worked. You are a genius.

I am sure you will hear from me more!

Now all i have to do is understand you formula to learn and pick it up myself!

 

Cheers Again.

 

Firoz

Thanks mate, glad to assist. And it just takes a bit of learning and understanding how it all works and pieces together.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ  - Mate what am i doing wrong here with this "Actual" curve?

As per the PBI Query Data, I have actual until June-17. The Curve stops correctly in June-17, but the "cumulative" calculation does not seem to work. Thanks again.

 

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

 

 

PBI GraphPBI GraphPBI QueryPBI Query

What I would suggest doing is for your Variable put this into a separate measure to ensure that it is returning the last value as you are expecting it to "LASTNONBLANK('Progress Curve'[Period], [Progress % Actual]) "

As that might be why it is stopping short.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thanks @GilbertQ for getting back to me.

So i created a new measure: Var LNBD = LASTNONBLANK('Progress Curve'[Period], [Progress % Actual])

and updated Cumulatve Actual = CALCULATE([Progress % Actual],FILTER(ALL('Progress Curve'[Period]), 'Progress Curve'[Period] <= [Var LNBD]))

 

Unfortunately No luck.

 

Cumulative Actual is calculating to be same value Progress % Actual i.e the value is not being calculated as running total.

See table below:

table.PNG

It appears to be not working as expected. Another option is to hardcode the Var LNBD to see if that works as expected.

The reason for the hardcoding is so that it can eliminate the Cumulative measure is working.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Tried using the below (25th June 2016)

Var LNBD = date(2017,06,25)

 

Flatline-7.PNG

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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)

 

Table-2.PNG

 

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

 

Table-3.PNG

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors