Forum Discussion
Solved
Cumulative Daily Average Balance
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 |
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) + 1return divide( SUMX( FILTER(all('Table'[AS_ON_DATE],'Table'[BALANCE]), 'Table'[AS_ON_DATE] <= _maxDate), 'Table'[BALANCE]), _days)
2 Replies
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) + 1return divide( SUMX( FILTER(all('Table'[AS_ON_DATE],'Table'[BALANCE]), 'Table'[AS_ON_DATE] <= _maxDate), 'Table'[BALANCE]), _days)Thanks!!It worked
