Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pnm_100
Frequent Visitor

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.

 

pnm_100_0-1675860711498.png

 

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

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

View solution in original post

7 REPLIES 7
tamerj1
Super User
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] )
        )
    )
)

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:

pnm_100_0-1675862370035.png

 

This is what happens with your formula:

pnm_100_1-1675862477530.png

 

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.

 

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

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

 

pnm_100_0-1675867160061.png

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!

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

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors