Forum Discussion

kenyonca's avatar
kenyonca
Advocate I
9 years ago
Solved

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 time...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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.:smileyhappy:

     

    Regards

kenyonca's avatar
kenyonca
Advocate I
9 years ago

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's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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.:smileyhappy:

 

Regards

  • kenyonca's avatar
    kenyonca
    Advocate I
    9 years ago

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