Forum Discussion

pnm_100's avatar
pnm_100
Frequent Visitor
3 years ago
Solved

How to remove data from future dates in a cumulative calculation

Hi All,   I have an S-Curve graph and I am trying to remove the data in the future dates of the cumulative line in the graph.    This is a picture of the s-curve graph.     For the cumul...
  • pnm_100's avatar
    pnm_100
    3 years ago

    Thanks for the help, but I finally found a solution. 

     

    Change the Cumulative Count Actual to:

     

    Cumm Count Actual = VAR CumCountAct = CALCULATE([Count Actual],USERELATIONSHIP('Calendar Table Plan'[Planned Date],Catalogue[Actual Completion Date]), FILTER(ALL('Calendar Table Plan'),'Calendar Table Plan'[Planned Date]<=MAX('Calendar Table Plan'[Planned Date]))) RETURN IF(MAX('Calendar Table Plan'[Planned Date]) <= TODAY(),CumCountAct,BLANK())
pnm_100's avatar
pnm_100
Frequent Visitor
3 years ago

Hey, sorry I did share the Count Actual formula in my first post, but I didn't realive the other formula has a "bleep" in front of it lol. The bleep is supposed to be Cumulative, and I had the first three letters of the word to shorten it. Guess that is a swear word here.

 

Here is the formulas again:

 

Count Actual = CALCULATE(COUNT(Catalogue[Actual Completion Date]),USERELATIONSHIP('Calendar Table Plan'[Planned Date],Catalogue[Actual Completion Date]))
 

Cumulative Count Actual = CALCULATE([Count Actual],USERELATIONSHIP('Calendar Table Plan'[Planned Date],Catalogue[Actual Completion Date]), FILTER(ALL('Calendar Table Plan'),'Calendar Table Plan'[Planned Date]<=MAX('Calendar Table Plan'[Planned Date])))

 

The formula you provided is the same as my cumulative. Thanks

pnm_100's avatar
pnm_100
Frequent Visitor
3 years ago

Thanks for the help, but I finally found a solution. 

 

Change the Cumulative Count Actual to:

 

Cumm Count Actual = VAR CumCountAct = CALCULATE([Count Actual],USERELATIONSHIP('Calendar Table Plan'[Planned Date],Catalogue[Actual Completion Date]), FILTER(ALL('Calendar Table Plan'),'Calendar Table Plan'[Planned Date]<=MAX('Calendar Table Plan'[Planned Date]))) RETURN IF(MAX('Calendar Table Plan'[Planned Date]) <= TODAY(),CumCountAct,BLANK())