Forum Discussion

elimey's avatar
elimey
Icon for Helper I rankHelper I
1 year ago
Solved

Excel formula to PowerBI DAX

I have an excel report that needs to convert to PowerBI. 

The reprot looks like this: 

 

Each Outstanding value uquals to the sum of Repayment values from next cell down, for instance:
Outstanding in cell C3 equals to Sum of Repayments from B4 to end (SUM(B4:B11)). 

 

 

How can I translate this in PowerBI?

Need Help
  • Deku's avatar
    Deku
    1 year ago

     

    You can use this to create the requested calculated column

    Outstanding =
var dt = datum[Date]
CALCULATE(
   SUM( datum[Replayments] ),
   datum[Date] > dt
)

     

4 Replies

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

     

    You can use this to create the requested calculated column

    Outstanding =
var dt = datum[Date]
CALCULATE(
   SUM( datum[Replayments] ),
   datum[Date] > dt
)

     

    • elimey's avatar
      elimey
      Icon for Helper I rankHelper I
      1 year ago

      can you elaborate? 
      you formula basically saying 
      datum[Date] > datum[Date]

      • Deku's avatar
        Deku
        Icon for Super User rankSuper User
        1 year ago

        sorry should be this

        CALCULATE(           -- convert row to filter context to access entire table
    Sum('Table'[amount]),
    ALL('Table')     -- remove filters on table from context transition
    ,'Table'[date] > EARLIER('Table'[date]) -- filter to rows in the future
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi elimey ,

     

    We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
    If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

    Regards,
    B Manikanteswara Reddy