Forum Discussion

LadyPeshet's avatar
LadyPeshet
Frequent Visitor
2 years ago

Calculation in DAX or Power Query based on Excel formula

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!

 

 

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    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