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 - fir...
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.

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.