Reply
Frequent Visitor
Posts: 13
Registered: ‎02-20-2017
Accepted Solution

Cumulative Line with Smart End Date

I'm trying to have my cumulative lines automatically stop projecting once they have reached their last data point.

 

As of now I have 5 different cumulative lines which all finish at different times.

 

The way that I have my data arranged is using the Query Editor. I needed to use this because there were 8 different date columns for a single line such as:

- Planned Draft Complete Date (cumulative)

- Design Need Date (cumulative)

- IFC Work Plan Date (cumulative)

- Construction Start Date (cumulative)

- Actual Draft Start Date

- Actual Draft Finish Date (cumulative)

 

The Attribute is the title of the columns (Draft Work Plan Date, Design Need Date, etc.)

The Value is the date.

 

With this to make the cumulatives "simpler" I made a column that just displayed a value of 1 if it was for that corresponding Attribute. For example

Planned Draft Complete = if('CWP Status'[Attribute]="CWP Plan Finish",1,0)

 

As a result I have used the following formula for the "Planned Draft Complete Date (cumulative)"

Cumulative Draft Plan = CALCULATE (
    SUM( 'CWP Status'[Planned Draft Complete] ) ,
 FILTER (
        ALL ( 'CWP Status' ) ,
 'CWP Status'[Value] <= MAX ( 'CWP Status'[Value] )
 )
)

2017-03-14 10_07_28-CWP Status - Power BI Desktop.png


Accepted Solutions
Highlighted
Super Contributor
Posts: 3,109
Registered: ‎07-17-2016

Re: Cumulative Line with Smart End Date

Hi @kenyonca,

 

A little weird. As the measure "Cumulative Draft Plan" is Cumulative, it shouldn't produce any gaps.

 

Could you help confirm the followings:

 

1. Now you should have three measures for "Draft Plan" in you mode.

Cumulative Draft Plan = CALCULATE (
    SUM( 'CWP Status'[Planned Draft Complete] ) ,
 FILTER (
        ALL ( 'CWP Status' ) ,
 'CWP Status'[Value] <= MAX ( 'CWP Status'[Value] )
 )
)
Cumulative Draft Plan Last Month =
CALCULATE (
    [Cumulative Draft Plan],
    DATEADD ( 'YourDateTable'[Date], -1, MONTH )
)
New Cumulative Draft Plan =
IF (
    [Cumulative Draft Plan] > [Cumulative Draft Plan Last Month],
    [Cumulative Draft Plan],
    BLANK ()
)

2. And you should show the last measure "New Cumulative Draft Plan" on the chart.

 

If you have done that, and it still doesn't work, could you share a sample pbix file which can reproduce the issue? So that we can help further investigate on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.Smiley Happy

 

Regards

View solution in original post


All Replies
Super Contributor
Posts: 3,109
Registered: ‎07-17-2016

Re: Cumulative Line with Smart End Date

Hi @kenyonca,

 

According to your description above, I would suggest you create a new measure to calculate Cumulative Draft Plan for Last Month first.

Cumulative Draft Plan Last Month =
CALCULATE (
    [Cumulative Draft Plan],
    DATEADD ( 'YourDateTable'[Date], -1, MONTH )
)

Then you should be able to use the formula below to create a new measure to calculate Cumulative Draft Plan which will stop projecting once they have reached their last data point.Smiley Happy

New Cumulative Draft Plan =
IF (
    [Cumulative Draft Plan] > [Cumulative Draft Plan Last Month],
    [Cumulative Draft Plan],
    BLANK ()
)

 

Regards

Frequent Visitor
Posts: 13
Registered: ‎02-20-2017

Re: Cumulative Line with Smart End Date

Hey @v-ljerr-msft when I try that solution it gives me gaps in the cumulative line. I am hoping to get a continuous line that would just be flat for any of the gaps. This becomes quite noticeable when I use any data slicers.

 

I tried to modify the "Cumulative Draft Plan Last Month" formula to be

Cumulative Draft Plan Last Month =
CALCULATE (
    [Cumulative Draft Plan],
    DATEADD ( 'YourDateTable'[Date], -2, MONTH )
)

 

However, when I do this it just extends out an extra month from when the last Draft Plan occurred.

Any ideas of how to fill in these gaps?

Power BI Gap in Cumulative.png

Highlighted
Super Contributor
Posts: 3,109
Registered: ‎07-17-2016

Re: Cumulative Line with Smart End Date

Hi @kenyonca,

 

A little weird. As the measure "Cumulative Draft Plan" is Cumulative, it shouldn't produce any gaps.

 

Could you help confirm the followings:

 

1. Now you should have three measures for "Draft Plan" in you mode.

Cumulative Draft Plan = CALCULATE (
    SUM( 'CWP Status'[Planned Draft Complete] ) ,
 FILTER (
        ALL ( 'CWP Status' ) ,
 'CWP Status'[Value] <= MAX ( 'CWP Status'[Value] )
 )
)
Cumulative Draft Plan Last Month =
CALCULATE (
    [Cumulative Draft Plan],
    DATEADD ( 'YourDateTable'[Date], -1, MONTH )
)
New Cumulative Draft Plan =
IF (
    [Cumulative Draft Plan] > [Cumulative Draft Plan Last Month],
    [Cumulative Draft Plan],
    BLANK ()
)

2. And you should show the last measure "New Cumulative Draft Plan" on the chart.

 

If you have done that, and it still doesn't work, could you share a sample pbix file which can reproduce the issue? So that we can help further investigate on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.Smiley Happy

 

Regards

Frequent Visitor
Posts: 13
Registered: ‎02-20-2017

Re: Cumulative Line with Smart End Date

@v-ljerr-msft I was able to get it work eventually....

I kept all the formulas the same as yours except for the following:

 

Cumulative Plan Draft =
IF (
    [Cumulative Draft Plan] > [Cumulative Draft Plan Last Month]  ,
    [Cumulative Draft Plan] ,
  IF(
   [Cumulative Draft Plan Last Month] = SUMMARIZE( 'CWP Status' , 'CWP Status'[Total Plan CWP Draft] ) ,
   BLANK () ,
   IF(
    [Cumulative Draft Plan Last Month] = [Cumulative Draft Plan],
    [Cumulative Draft Plan],
    blank()
   )
  )
)

 

To make this work I needed to create a column that all it did was total the CWP Plans

Total Plan CWP Draft = sum ( 'CWP Status'[Planned Draft Complete] )

 

Not sure why I couldnt just replace the "summarize" formula with SUM ( 'CWP Plan Status' [ Planned Draft Complete])

 

Thanks for the help!!!