Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
@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] )
) )
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
26 | |
20 | |
13 | |
8 |
User | Count |
---|---|
76 | |
48 | |
46 | |
20 | |
16 |