 # Cumulative Biennial budget formula

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.

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:

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

Happy New Year!

1 Like

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!!