Forum Discussion
average open balance, a dax question
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:
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
2 Replies
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