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

[pedroccamaraDBI]

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

[v-xiaotang · Accepted solution]

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.

[Fowmy]

@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]

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

[Fowmy]

@pedroccamaraDBI 

Then, kindly provide more details and sample data with the expected results.

[pedroccamaraDBI]

Hi @Fowmy i believe my sample above is enough to get the 2 measures i need : the balance of the min date selected in filter dates, and the balance of the max date selected. Am i missing something?