Solution to stop cumulative actuals line chart on last month of actuals instead of extending flat

[Anonymous]

I'm building a cumulative cost over time line chart (budget/forecast/actual).  The light green line is Cumulative Actuals, and I'm looking for a solution for the line to stop in the most recent month with actuals instead of extending the flat line to future months.  Below is the current measure for deriving the cumulative line.  Can I add another filter in this measure or create another measure to determine what the last date where Actuals are not blank so the line stops on that date?  Thank you.

Cumulative Actuals =

CALCULATE(

SUM('Finance Sheet'[Actual]),

FILTER(

ALLSELECTED('Finance Sheet'[DateFinance]),

ISONORAFTER('Finance Sheet'[DateFinance], MAX('Finance Sheet'[DateFinance]), DESC)

)

)

[Anonymous]

[v-lid-msft]

Hi @Anonymous ,

We can change the formula as following to meet you requirement:

Cumulative Actuals =
VAR maxActualDate =
    CALCULATE ( MAX ( 'Finance Sheet'[DateFinance] ), ALL ( 'Finance Sheet' ) )
RETURN
    IF (
        MAX ( 'Finance Sheet'[DateFinance] ) <= maxActualDate,
        CALCULATE (
            SUM ( 'Finance Sheet'[Actual] ),
            FILTER (
                ALLSELECTED ( 'Finance Sheet'[DateFinance] ),
                ISONORAFTER (
                        'Finance Sheet'[DateFinance], MAX ( 'Finance Sheet'[DateFinance] ), DESC
                )
            )
        ),
        BLANK ()
    )

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I don't believe this works correctly.  Your maxActualDate appears to be finding the max value in DateFinance column.  However, I need to find the max value in DateFinance column where Actual column is not blank.  Any assistance is appreciated.

Thanks

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for late reply, we can try to create measure use the following formula.

Cumulative Actuals =
VAR maxActualDate =
    CALCULATE (
        MAX ( 'Finance Sheet'[DateFinance] ),
        FILTER ( ALLSELECTED ( 'Finance Sheet' ), 'Finance Sheet'[Actual] <> BLANK () )
    )
RETURN
    IF (
        MAX ( 'Finance Sheet'[DateFinance] ) <= maxActualDate,
        CALCULATE (
            SUM ( 'Finance Sheet'[Actual] ),
            FILTER (
                ALLSELECTED ( 'Finance Sheet'[DateFinance] ),
                ISONORAFTER (
                        'Finance Sheet'[DateFinance], MAX ( 'Finance Sheet'[DateFinance] ), DESC
                )
            )
        ),
        BLANK ()
    )

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I've just sorted this for myself, try using the following; 

Total Actual Costs = CALCULATE(SUM('Actuals + CTC'[Actuals Weekly]),

FILTER (ALL ('Date'[Date]),'Date'[Date] <= MAX ('Actuals + CTC'[Week Ending (Fri)])),

FILTER (ALL ('Date'[Date]), 'Date'[Date]>date(2018,06,01))

)

Where 'Actuals + CTC'[Week Ending(Fri)] was the date column in my excel table, date[date] was my date table & 'Actuals + CTC'[Actuals Weekly] was the weekly costs in my data table.

See if you can figure this out by chopping & Changing what you need.