topic Re: Calculation in DAX or Power Query based on Excel formula in DAX Commands and Tips

Calculation in DAX or Power Query based on Excel formula

LadyPeshet — Thu, 14 Dec 2023 13:01:20 GMT

Hello,

I have following calculation and I need to create results in Power BI that are right now placed in the last column "Diff Amount". Diff Amount doesn't exist currently in Power BI model.

Monthly Amount = column B

Minimum Monthly Amount = column C

Diff Amount = column D

	Monthly Amount	Minimum Monthly Amount	Diff Amount
Month
01.2011	200	100	0
02.2011	100	100	0
03.2011	90	100	10
04.2011	103	100	7
05.2011	110	100	0
06.2011	50	100	50
07.2011	140	100	10
08.2011	115	100	0
09.2011	100	100	0

Excel formula is following: =IF((C3-B3+D2)<0;0;(C3-B3+D2))

"Diff Amount" is referencing to previous row results. First two column are already in datasource. Is it possible to replicate third column without phisically adding it to the datasource? Would it be possible to calculate it using custom function in Power Query or by using DAX?

Thank you in advance for your help!

Re: Calculation in DAX or Power Query based on Excel formula

lbendlin — Mon, 18 Dec 2023 00:30:43 GMT

DAX cannot do dynamic self referencing. Your only option is Power Query with List.Accumulate.

Re: Calculation in DAX or Power Query based on Excel formula

BeaBF — Mon, 18 Dec 2023 09:52:18 GMT

LadyPeshet Here a sample code:

Diff Amount =
VAR CurrentRow = YourTable[Monthly Amount]
VAR PreviousDiff = IF(MAX(YourTable[Month]) <> MIN(YourTable[Month]), LOOKUPVALUE(YourTable[Diff Amount], YourTable[Month], MAX(YourTable[Month])-1), 0)
RETURN
IF((YourTable[Minimum Monthly Amount] - CurrentRow + PreviousDiff) < 0, 0, YourTable[Minimum Monthly Amount] - CurrentRow + PreviousDiff)

BBF