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())
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

pnm_100 
Please try

**bleep** Count Actual =
IF (
    NOT ISBLANK ( [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] )
        )
    )
)
pnm_100's avatar
pnm_100
Frequent Visitor
3 years ago

So that worked, but the issue I have now is the Count Actual has a blank value in 2020-Q2, see below:

 

So there is now a break in the line in the graph. How do I fix the Count Actual to be zero if it is blank? Thanks!

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    pnm_100 
    Would you please try the following. Whatever the result would be, please share the dax for [Count Actual], that will provide some insights about your data model.

    Cumm. 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 ( Catalogue[Actual Completion Date] )
    )
)

     

    • 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())