Dynamic measure (opening balance) to act like hard coded number

DAX DAX Calculations
1

Hey all,

I would like to make a measure act like a hard coded number and add an opening balance to a cumulative monthly total on a table that can dynamically update each month.

I have a balance sheet table for inventory. In my report I need to show the last 12 month’s cumulative balance. This updates at each month end. In the file you can see the desired result where I have added in the measure, the opening balance as a hard coded number.

But when I try and replace the hard coded number with my measure, I get an incorrect balance on my inventory table.

Sample file is here:

Connected date table: is updated each month, filters the inventory opening balance to return as a scalar value.

Disconnected date table: is also updated each month to filter a table that shows only the last 12 month’s amounts.

I hope this is clear, please let me know if you need more information. Basically, I’m trying to get to the table “desired result” without having to change the Variable “InventoryOpeningBalance” hard coded number every month. Related Measure:

Inventory Last 12M = 
VAR InventoryOpeningBalance = 8453112 // [Inventory Opening Balance]  //
VAR FYMOffset = 
LOOKUPVALUE( Dates[MonthOffset], Dates[Date], [Harvest Max DisconnDate] )

VAR Result =
CALCULATE(
    [Inventory Balance],
    FILTER( 
        ALL( Dates ),
        Dates[Date] <= MAX( Dates[Date] ) &&
        Dates[MonthOffset] <= FYMOffset &&
        Dates[MonthOffset] >  FYMOffset -12
    )
) + InventoryOpeningBalance
VAR FiltResult =
IF( SELECTEDVALUE( Dates[MonthOffset] ) <= FYMOffset &&
        SELECTEDVALUE( Dates[MonthOffset] ) >  FYMOffset -12,
        Result,
        BLANK()
)

RETURN FiltResult
2

Hi,

Can you tell me more about your opening balance .
How we can get this number 8453112.

Thanks,
Anurag

1 Like
3

The opening balance can be seen on the chart “Full inventory table for reference” as is the balance as at FY21 September, $8,453,112

it’s the opening balance + that month’s transactions = closing balance of the month (can see in the “full inventory for reference”) therefore giving you the inventory cumulative balance.

I’ve just reuploaded my data file, can get the updated file here, the filter below was set incorrectly

4

Hi,

One more question your opening balance change on selection or it changes as we move like today we are in oct fy22.
It depend on today or on other criteria.

Thanks

1 Like
5

the opening balance will change as we move through the year, this month the reporting date is September FY22 and 12 months before is the opening balance.

Next month the reporting date is October FY22 so the opening balance will be 12 months before that. The opening balance will depend on the selection of the filter.

Each month the manual intervention will need to be changing the selection of the 2 filters in the report. (disconnected date table and date table)

6

Hi,

I try to solve your problem by adding new column in your date table open which take max date from balance sheet and go exactly 12 month back and i mark it as “op”

And after that i use that column in your opening measure and i think i got your req result
image

Attaching the pbix file for your refrence:
Mock Data PBI.pbix (778.3 KB)

Let me know is this what you want.
By this approach every time the sheet updated opening balances changes automatically.

1 Like
7

Hi @Timmay

Try change your measure for opening balance with:

Inventory Opening Balance = 
VAR _MonthOffset = SELECTEDVALUE(Dates[MonthOffset]) - 1

VAR OpeningBalance = 
CALCULATE( 
    [Inventory Balance],
    FILTER( 
        ALL( 'Dates' ),
        Dates[MonthOffset] <= _MonthOffset
    )
)

RETURN IF([Inventory Last 12M] <> BLANK(), OpeningBalance)
// amount to change as each month as selected by the connected date table
8

I like your solution but unfortunately when i tried to change the date, the amount was incorrect.

However your solution give me an idea on how to solve it and it worked, it was actually much more simple than I thought.

I just added a “cumulative total” column to the balance sheet with this formula:

Cumulative Total = CALCULATE(SUM([Balance]), FILTER('Balance_Sheet','Balance_Sheet'[Date]<=EARLIER('Balance_Sheet'[Date])))

and then created a measure to sum up the column to the disconnected date table:

Inventory Solution = 
VAR FYMOffset = 
LOOKUPVALUE( Dates[MonthOffset], Dates[Date], [Harvest Max DisconnDate] )

VAR Result =
sum(Balance_Sheet[Cumulative Total])

VAR FiltResult =
IF( SELECTEDVALUE( Dates[MonthOffset] ) <= FYMOffset &&
        SELECTEDVALUE( Dates[MonthOffset] ) >  FYMOffset -12,
        Result,
        BLANK()
)

RETURN FiltResult

file with solution is here if you are interested:

Many thanks,
Tim

1 Like