average open balance, a dax question

[Anonymous]

hello!

I have a dax question concerning average open balance. My data looks like this:

I have linked a date calendar table to my fact table. 

I have created an open balance measure:

CALCULATE(SUM('table'[transaction]), FILTER(ALLSELECTED(dim_Calendar), dim_Calendar[Date] <= MAX(dim_Calendar[Date]))

This gives me the correct open balance for every day on the calendar.

Now I would like to have an average value for open balance within a certain time period. I have tried way too long with no succes. Can someone help me or point in right direction? Thanks so much in advance!

[Anonymous · Accepted solution]

@Anonymous , I think this measure might do what you are after:

Avg Balance = 
    VAR vFirstDate = FIRSTDATE(ALLSELECTED(_Date[Date]))
    VAR vLastDate = LASTDATE('_Date'[Date])
    RETURN
        AVERAGEX(
            CALCULATETABLE(
                '_Date'
                ,_Date[Date] >= vFirstDate
                , _Date[Date] <= vLastDate
            )
            ,[Open Balance]
        )

With your test data, here are the Avg Balance it gives for each day in Jan 2022. For the month of Jan 2022, the average balance is 4000 which I think it right

9 days @ 5000 = 45000

5 days @ 3000 = 15000

15 days @ 4000 = 60000

2 days @ 2000 = 4000

total = 124000 / 31 days = 4000

[Anonymous]

Amazing! Thank you so much. Time for more dax training for me 🙂