Cumulative Line with Smart End Date

[kenyonca]

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

[v-ljerr-msft]

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.

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

Regards

[kenyonca]

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?

[v-ljerr-msft · Accepted solution]

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.

Regards

[kenyonca]

@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!!!