Cumulative Daily Average Balance

[PriyankaA905]

Hi

In below table, How can we calculate No of Days and CDAB calculation in Power BI

For Eg as on 31-May-22
SUM_Balance_FY_MONTH = SUM of balance from 1 April 2022, i.e FY 2022-2023, 3687
No of days= NO of days from Finanicial year i.e from 1April 2022 to 31-May-22 ,61
CDAB = Sum_Balance_FY_Month/No of days

AS_ON_DATE	BALANCE	SUM_BALANCE _FY_MONTH	No of Days	CDAB
1-Apr-22	326	326	1	326
2-Apr-22	240	566	2	283
3-Apr-22	434	1000	3	333
4-Apr-22	309	1309	4	327
5-Apr-22	200	1509	5	302
6-Apr-22	378	1887	6	315
30-Apr-22	600	2487	30	82.9
31-May-22	1200	3687	61	60.4

[PriyankaA905]

Thanks!!It worked

[d_gosbell · Accepted solution]

You could do the CDAB calculation in a single expression like the following:

CDAB =

var _maxDate = max('Table'[AS_ON_DATE])

var _minDate = calculate(min('Table'[AS_ON_DATE]), all('Table'[AS_ON_DATE]))

var _days = DATEDIFF(_minDate, _maxDate, DAY) + 1

return divide( SUMX( FILTER(all('Table'[AS_ON_DATE],'Table'[BALANCE]), 'Table'[AS_ON_DATE] <= _maxDate), 'Table'[BALANCE]), _days)