Forum Discussion
DAX Help
Hi,
I am trying to replicate the below Excel Calculation in Power BI: (Calculated Column)
Formula:
AD Line = AD Line (previous value) + Net Advances (Current Value)
For the 1st row, AD Line = Net Advances
Sample Data:
| Date | Advances | Declines | Net Advances | AD Line |
| 1/1/2019 | 1456 | 1549 | -93 | -93 |
| 1/2/2019 | 571 | 2470 | -1899 | |
| 1/3/2019 | 1328 | 1684 | -356 |
I appreceate any help!
Thanks
Hi Anonymous ,
Recursive calculated columns are a nightmare to be honest, but I got around it in your example. Your AD line is basically the SUM of all net advances of every row before the current row, plus the net advances of the current row. So, your AD Line will be calculated using the following calculated column:
AD Line2 = VAR curDate = Table2[Date] RETURN CALCULATE(SUM(Table2[Net Advances]), FILTER(ALL(Table2), Table2[Date] <= curDate))Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
5 Replies
Hello SV12,
Your current value is today or a particulair day?
Also if you need previous value, what is the value expected ?
Could you give an exemple please?
BR
Hi nlefalher ,
Thanks for your response.
Current value is the current row for the calculation of column AD Line.
Previous value is the value of the previous row in the same column (AD Line)
Example: https://school.stockcharts.com/doku.php?id=market_indicators:ad_line
Thanks
Anonymous ,
I am not an expert but I would say something like :
CALCULATE( SUM(Sales[AD]) , 'Calendar'[Date] = today()-1 )
Hi Anonymous ,
Recursive calculated columns are a nightmare to be honest, but I got around it in your example. Your AD line is basically the SUM of all net advances of every row before the current row, plus the net advances of the current row. So, your AD Line will be calculated using the following calculated column:
AD Line2 = VAR curDate = Table2[Date] RETURN CALCULATE(SUM(Table2[Net Advances]), FILTER(ALL(Table2), Table2[Date] <= curDate))Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
JarroVGIT Thank you so much! That worked 🙂
