Cumulative totals in biennial budget

DAX DAX Calculations
1

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.

2

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

image
image.png512×588 14 KB

Hope this is helpful.

1 Like
3

Perfect, thanks so much.

4

:+1:. Glad to help.

  • Brian