DAX Help

[sv12]

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

[JarroVGIT · Accepted solution]

Hi @sv12 ,

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 🙂

[sv12]

@JarroVGIT  Thank you so much! That worked 🙂

[nlefalher]

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

[sv12]

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

[nlefalher]

@sv12 ,

I am not an expert but I would say something like :

CALCULATE(
    SUM(Sales[AD])
    , 'Calendar'[Date] = today()-1
)