Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Post Partisan
5 years ago
Solved

Balance difference between two dates (power query, or...)

Hello everyone. I hope everyone's ok.

I need to check the balance difference between the dates selected on the filter. I have the filter for year and also for months.
That means, last balance - first balance, maybe something like, balance (MAX(dates)) - balance(MIN(Dates)), maybe.
My table is like this:

Every row will have another column that identifies the type of income or costs like, clients of suppliers for example. And that's what i will need. I will need to know the balance diff for clientes, suppliers, etc.
I really hope you guys can help me
Thanks a lot
Pedro

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi pedroccamaraDBI 

    Is column balance a measure? then you want to get the last balance and first balance of the selected period?
    if yes, one quick example bellow,

    in the example, date between 2016/1/1, 2016/1/25. then I create a slicer and set the period 

    then, create the measure,

    Measure = 
var _mindate=CALCULATE(MIN('Table'[orderdate]),ALLSELECTED('Table'))//get the min date of selected period
var _maxdate=CALCULATE(MAX('Table'[orderdate]),ALLSELECTED('Table'))
var _firstbalance=CALCULATE([balance],ALLSELECTED('Table'),'Table'[orderdate]=_mindate)// get the firstbalance of selected period
var _lastbalance=CALCULATE([balance],ALLSELECTED('Table'),'Table'[orderdate]=_maxdate)
return _lastbalance-_firstbalance

     

    Best Regards,

    Community Support Team _ Tang

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

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    pedroccamaraDBI 

    You can add a new column to your table as follows and it will show the difference between Debit and Credit. You should be able to filter by Client and Supplier as well.

    Balance = Table[Debit] - Table[Credit]

     


    • pedroccamaraDBI's avatar
      pedroccamaraDBI
      Post Partisan
      5 years ago

      Hello Fowmy 
      Thank you for your answer but it's not what i asked and that column is already there

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi pedroccamaraDBI 

    Is column balance a measure? then you want to get the last balance and first balance of the selected period?
    if yes, one quick example bellow,

    in the example, date between 2016/1/1, 2016/1/25. then I create a slicer and set the period 

    then, create the measure,

    Measure = 
var _mindate=CALCULATE(MIN('Table'[orderdate]),ALLSELECTED('Table'))//get the min date of selected period
var _maxdate=CALCULATE(MAX('Table'[orderdate]),ALLSELECTED('Table'))
var _firstbalance=CALCULATE([balance],ALLSELECTED('Table'),'Table'[orderdate]=_mindate)// get the firstbalance of selected period
var _lastbalance=CALCULATE([balance],ALLSELECTED('Table'),'Table'[orderdate]=_maxdate)
return _lastbalance-_firstbalance

     

    Best Regards,

    Community Support Team _ Tang

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