Forum Discussion

Hassaan1994's avatar
Hassaan1994
New Member
3 years ago
Solved

Calculating balance column based on multiple criteria

Hi Everyone! 

 

I'm very new to using Power BI and would really appreciate some help. 

 

I'm trying to calculate a column for balance which shows the remaining amount over due, and should update after the company changes. Currently, the balance column is executing an IF query as follows: 

 

Balance = IF(AND('For BI'[Date]>DATE(2022,9,19),'For BI'[Overdue]>0),('For BI'[Overdue]-'For BI'[Collection]),'For BI'[Overdue])

 

What I want is for the balance amount to update for each company after each date, for example:

 

the balance in row # 5 should be: 11,281-300 (which is the sum of collections after the 19th) 

This should continue until the rows for the current company end and move to the next one.

 

 

I'd be really thankful if someone's able to help out 🙂

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Hassaan1994 

    Here are the steps you can follow:

    (1)This is my test data:

    (2)you can create a calculated column :

    Balance = var _current_IPP= 'Sheet1'[IPP Name]
var current_date = 'Sheet1'[Date]
var _sum=SUMX( FILTER('Sheet1','Sheet1'[IPP Name]=_current_IPP && 'Sheet1'[Date] <= current_date && 'Sheet1'[Date] > DATE(2022,9,19)) , [Collection])
return 
[Overdue]-_sum

    (3)Then you cna meet your need, the result is as follows:

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , Hassaan1994 

    Here are the steps you can follow:

    (1)This is my test data:

    (2)you can create a calculated column :

    Balance = var _current_IPP= 'Sheet1'[IPP Name]
var current_date = 'Sheet1'[Date]
var _sum=SUMX( FILTER('Sheet1','Sheet1'[IPP Name]=_current_IPP && 'Sheet1'[Date] <= current_date && 'Sheet1'[Date] > DATE(2022,9,19)) , [Collection])
return 
[Overdue]-_sum

    (3)Then you cna meet your need, the result is as follows:

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

    • Hassaan1994's avatar
      Hassaan1994
      New Member
      3 years ago

      Thank you! This works perfectly.

      I just added an IF statement after return, to make sure my balance doesn't show below zero.