cancel
Showing results for
Did you mean:
New Member

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!

1 ACCEPTED SOLUTION
New Member

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 7
Super User

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] )
)
)
)``````
New Member

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.

Super User

@pnm_100

``````**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] )
)
)
)``````
New Member

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!

Super User

@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] )
)
)``````

New Member

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

New Member

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

Announcements

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors