cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PriyankaA905
Regular Visitor

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_DATEBALANCESUM_BALANCE _FY_MONTHNo of DaysCDAB
1-Apr-223263261326
2-Apr-222405662283
3-Apr-2243410003333
4-Apr-2230913094327
5-Apr-2220015095302
6-Apr-2237818876315
30-Apr-2260024873082.9
31-May-22120036876160.4
1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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)

View solution in original post

2 REPLIES 2
PriyankaA905
Regular Visitor

Thanks!!It worked

d_gosbell
Super User
Super User

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)

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!