Cumulative totals in biennial budget

DAX Cumulative RevExp for biennium.pbix (3.8 MB)

My Jan 2020 cumulative revenues and expenses are populating in Jan 2021 also, which populates the entire year of numbers instead of stopping at the current month. I think my formula is correct, but it may have something to do with my using a biennial budget cycle. Thanks for any help you can provide.

@lmcvay,

Suggested fix below. Exact same pattern applied for cumulative revenue measure. Full solution file posted below.

Cumulative Expense = //Stops at the current month

VAR FirstDateOfMonth =
DATE(
    SELECTEDVALUE( Dates[Year] ),
    SELECTEDVALUE( Dates[MonthNumber] ),
    1
)

RETURN
IF( FirstDateOfMonth > TODAY(),
    blank(),
    CALCULATE(
        SUM( Budget[YTD_Expense]),
        FILTER(
            ALLSELECTED(Dates),
            'Dates'[Date] <= MAX ( Dates[Date])
        )
    )
)

Hope this is helpful.

1 Like

Perfect, thanks so much.

:+1:. Glad to help.

  • Brian