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

 

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

 

TABLE-4.PNG

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

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

 

Curve-2.PNGCurve-1.PNG

Thanks @GilbertQ for your prompt response. But unfortunately, didnt work. 

Flatline-2.PNG

 

 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

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