topic Re: Running Totals are not considering the values from IF DAX. in DAX Commands and Tips

Running Totals are not considering the values from IF DAX.

DivakarKrishnan — Mon, 21 Sep 2020 07:26:07 GMT

Dear Team,

I am calculating cumulative balance using DAX, my DAX is not giving correct running totals.

Issue is happening due to this “Funding” measure part, if I ignore / comment out this Funding measure then my running totals are coming correctly. So basically my cumulative DAX is not considering this “Funding” measure. Please advise on how to correct this one? Tried many workarounds but none of them is working for my case.

DAX 1

Balance Current Month Updated =

[TB Amount Total] + [ToCollect] - [Forecasted payments v2] + [Funding]

DAX 2

Funding = IF(

[Balance Current Month] < 0,

[Balance Current Month]*-1,10000000

)

DAX 3

Cumulative Balance Updated =

VAR Result =

CALCULATE (

[Balance Current Month Updated],

FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX('Date'[Date] )

)

RETURN

Result

Re: Running Totals are not considering the values from IF DAX.

amitchandak — Mon, 21 Sep 2020 08:56:54 GMT

DivakarKrishnan , Try like

Cumulative Balance Updated =

VAR Result =
var _1 = Sumx(summarize('Date', 'Date'[Month Year],"_1",[Balance Current Month Updated]),[_1])
return
CALCULATE (
_1, FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX('Date'[Date] )
) )

or
Cumulative Balance Updated =

VAR Result =
var _1 = Sumx(summarize('Date', 'Date'[Date],"_1",[Balance Current Month Updated]),[_1])
return
CALCULATE (
_1, FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX('Date'[Date] )
) )

Re: Running Totals are not considering the values from IF DAX.

DivakarKrishnan — Mon, 21 Sep 2020 09:15:56 GMT

Dear Amit amitchandak , Tried the both but not working..

When using Month Year, i am getting same figures as Balance Current Month Updated.

When using Date, i am getting below which is not correct and huge figures.

Re: Running Totals are not considering the values from IF DAX.

Anonymous — Mon, 21 Sep 2020 09:20:25 GMT

Not enough information to tell you where a problem is....

Re: Running Totals are not considering the values from IF DAX.

amitchandak — Mon, 21 Sep 2020 09:21:18 GMT

DivakarKrishnan , Your questions seem the same as this one? https://community.powerbi.com/t5/DAX-Commands-and-Tips/Cumulative-Balance-Issue/m-p/1382270#M25265

Check the suggestion there