Forum Discussion

PriyankaA905's avatar
PriyankaA905
Frequent Visitor
4 years ago
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 o...
POWER BI Dax rules
  • d_gosbell's avatar
    d_gosbell
    4 years ago

    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)
d_gosbell's avatar
d_gosbell
Super User
4 years ago

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)