Need help on Cumulative Sum to Stop at last value

[Anonymous]

Hi there, my cumulative sum is extending to further dates where there are blank values. I need it to stop when there is no more data. I am specifically looking at the green line in the chart on the left and the red/green lines on the right

Thank you

Baseline Weekly =

CALCULATE(

SUM(fact_Progress[Value]),

FILTER(ALLSELECTED(dim_Date),dim_Date[CurWeekOffset] <= MAX(dim_Date[CurWeekOffset])),

FILTER(ALLSELECTED(fact_Progress),fact_Progress[Type]="Target"))

[MFelix]

Hi  @Anonymous ,

Try to add the following code:

Baseline Weekly =
IF (
    MAX ( dim_Date[CurWeekOffset] )
        >= CALCULATE ( MAX ( fact_Progress[Date] ), ALL ( fact_Progress[Date] ) ),
    BLANK (),
    CALCULATE (
        SUM ( fact_Progress[Value] ),
        FILTER (
            ALLSELECTED ( dim_Date ),
            dim_Date[CurWeekOffset] <= MAX ( dim_Date[CurWeekOffset] )
        ),
        FILTER ( ALLSELECTED ( fact_Progress ), fact_Progress[Type] = "Target" )
    )
)

Replace the fact_Progress[Date] by the column date of your fact_progress.

Regards,

MFelix

[Anonymous]

I get the same result with that formula as my previous. Any other solutions?

[MFelix]

Hi,

Can you share a sample file please.

Regards,
MFelix

[Anonymous]

I can't share a sample file. The current week offset of my dim_date table is a numeric number based on the # of days from present (yesterday is -1, today is 0, tomorrow is +1). Could this be the problem with your formula?

[MFelix]

Hi @Anonymous ,

Without any other information about your model is difficult to help you.

Can you share the way your model is setup and a sample of the information so I can make some tests?

If you don't want to share the file because it's sensitive information you can do it through private message or make a mockup file with the structure and sample data.

Regards,

MFelix

[Anonymous · Accepted solution]

I got it. here is what I did (note table names are slightly different from original). I am guessing it is because my primary table had additional dates beyond the max target date, so I included a filter on the original max date calculation for "target" only. Thank you for your help

Original Baseline Weekly2 = 
VAR MaxDate = CALCULATE(MAX(fact_ManhourProgress[Date]),FILTER(fact_ManhourProgress,fact_ManhourProgress[Type]="Target"))
RETURN
    CALCULATE (
        SUM ( fact_ManhourProgress[Value] ),
        FILTER (
            ALLSELECTED ( dim_Date ),
            dim_Date[Date] <= MaxDate
        ),
        FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Type] = "Target" )
    )