Cumulative Biennial budget formula

DAX DAX Calculations
1

DAX cumulative biennial budget.pbix (2.5 MB)
My cumulative budget formula reflects a biennial budget allocated evenly over 24 months. The problem I have is that the cumulative formula resets in January but I need it to keep calculating through the second year.

2

@lmcvay,

Welcome to the forum!

I’m sure there are a lot of different ways to approach this one, but the approach I used was based on two calculated columns added to your date table. The first defines each biennial period:

Biennial Grouping = 

VAR LstYear = 
dCalendar[Year] - 1

VAR NxtYear = 
dCalendar[Year] + 1

RETURN
IF(
    ISEVEN( dCalendar[Year] ),
    CONCATENATE( dCalendar[Year], NxtYear ),
    CONCATENATE( LstYear, dCalendar[Year] )
)

The second re-numbers each month in the biennial period 1 to 24:

BiMoNumber = 

VAR RenumberMonth =
IF(
    ISEVEN( dCalendar[Year] ),
    dCalendar[MonthNumber],
    dCalendar[MonthNumber] + 12
)

RETURN
CALCULATE(
    RenumberMonth,
    ALLEXCEPT(
        dCalendar,
        dCalendar[Biennial Grouping]
    )
)

From there, it’s just search and replace in your cumulative measure, replacing month number with biennial month number:

Cumulative Budget Biennnial = 
SUMX( 
    FILTER(
        ALLSELECTED( dCalendar[BiMoNumber]),   
        dCalendar[BiMoNumber] <= MAX( dCalendar[BiMoNumber] )
    ),
    'PowerBI budget'[BudgetAllocation2]
)

Here it is all put together:

image
image.png977×605 46.1 KB

I hope this is helpful. Full solution file posted below.

Happy New Year!

3

This works perfectly. Had an issue at first because my model had the wrong data type for month number. Thanks so much for you help!!