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 cumulative line, I used two formulas: one to calculate the count of the Actuals and another to calculate the cumulative count. See formulas below:

 

1. 

Count Actual = CALCULATE(COUNT(Catalogue[Actual Completion Date]), USERELATIONSHIP('Calendar Table Plan'[Planned Date],Catalogue[Actual Completion Date]))
 
2.
**bleep** 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 calendar table plan is where I am getting the dates and it is connected to my main table. I have to use USERELATIONSHIP to make it work.
 
I am thining this is something simple, but I cannot figure it out even after reading various forum entries. Any help is greatly appreciated!

 

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

7 Replies

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

    Hi pnm_100 
    Pleasr try

    Count Actual =
IF (
    NOT ISEMPTY ( Catalogue ),
    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

      Hey tamerj1, thanks for the reply but unfortunatly I could get it to work. I attached a table of how the data should look and how it looks when I input your formula:

       

      This is the original:

       

      This is what happens with your formula:

       

      I feel as though it should be an IF with the date column, but the IF statement will not let me use the Calendar table.

       

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