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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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

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

 

Regards