Forum Discussion

nirrobi's avatar
nirrobi
Helper V
5 years ago

Running Total dependency based on previous date

Hi all,   I have the below data - first 3 columns ( date & move & open) the rule open balance - 180,000 the max amount is 800,000 the min amount is 150,000 the blue columns are for explanation...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi nirrobi ,

 

For 202103,we should compare 150000+150000 with 150000,so I get the result 300000,so here,the result is right,it should be 300000.

For 202108,I modified my column and measure expressions as below:

flag = 
var _mindate=CALCULATE(MIN('Table'[Date]),ALL('Table'))
Return
IF('Table'[Date]=_mindate,
IF('Table'[Movement]+'Table'[Open]<=150000,0,
IF('Table'[Movement]+'Table'[Open]>150000&&'Table'[Movement]+'Table'[Open]<800000,1,2)),
IF('Table'[Date]>_mindate,
var _date=CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Date]>=MINX('Table','Table'[Date])))
var _sum=CALCULATE(SUM('Table'[Movement]),FILTER('Table','Table'[Date]>=_date&&'Table'[Date]<MAX('Table'[Date])))
Return
IF(_sum+150000<=150000,0,
IF(_sum+150000>150000&&'Table'[Movement]+150000<800000,1,2))))
Measure = 
var _mindate=CALCULATE(MIN('Table'[Date]),ALL('Table'))
var _total=SUMX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])),'Table'[Movement])
    var _n=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])))
    var _previousdate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])))
    var _previousflag=CALCULATE(MAX('Table'[flag]),FILTER(ALL('Table'),'Table'[Date]=_previousdate))
    var _previousmove=CALCULATE(MAX('Table'[Movement]),FILTER(ALL('Table'),'Table'[Date]=_previousdate))
Return
IF(MAX('Table'[Date])=_mindate,
IF(MAX('Table'[flag])=0,MAX('Table'[Open]),
IF(MAX('Table'[flag])=1,MAX('Table'[Open])+MAX('Table'[Movement]),800000)),
 IF(MAX('Table'[Date])>_mindate,
    IF(_previousflag=0,
    150000,
    IF(_previousflag=1,
    var _date1=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[flag]=0))
    var _sum1=SUMX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Date]>=_date1&&'Table'[flag]=1),'Table'[Movement])+150000
    Return
    IF(_previousmove>0,
       IF(_sum1>150000&&_sum1<800000,
        _sum1,800000),
      IF(_previousmove<0,
         var _sum2=CALCULATE(SUM('Table'[Movement]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Date]>_mindate&&'Table'[Movement]>0))
         Return
          IF(_sum2<800000,_sum1,800000+_previousmove)))))))

And you will see:

For the updated .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

nirrobi's avatar
nirrobi
Helper V
5 years ago

thanks thanks and one more thanks.

 

I think all is ok now but 202109 as it should be 300000-100000=200000 but it shows 700000

 

am I missing something?

 

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  nirrobi ,

     

    Sorry for the mistake and late reply.

    Create a new column as below:

    flag2 = 
var _mindate=CALCULATE(MIN('Table'[Date]),ALL('Table'))
var _sum=SUMX(FILTER('Table','Table'[Date]>_mindate&&'Table'[Date]<EARLIER('Table'[Date])&&'Table'[Movement]>0),'Table'[Movement])
Return
IF(_sum>800000,1,BLANK())

    Then modify the measure as below:

    Measure = 
var _mindate=CALCULATE(MIN('Table'[Date]),ALL('Table'))
var _seconddate=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]>_mindate))
var _total=SUMX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])),'Table'[Movement])
    var _n=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])))
    var _previousdate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])))
    var _previousflag=CALCULATE(MAX('Table'[flag]),FILTER(ALL('Table'),'Table'[Date]=_previousdate))
    var _previousmove=CALCULATE(MAX('Table'[Movement]),FILTER(ALL('Table'),'Table'[Date]=_previousdate))
    var _previousdatenegtive=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Movement]<0))
    var _pdate1=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Movement]>0))
    var _pdate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<_previousdatenegtive&&'Table'[Movement]>0))
      var _sum3=CALCULATE(SUM('Table'[Movement]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Date]>_pdate))
      var _sum4=CALCULATE(SUM('Table'[Movement]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Date]>_pdate1))
Return
IF(MAX('Table'[Date])=_mindate,
IF(MAX('Table'[flag])=0,MAX('Table'[Open]),
IF(MAX('Table'[flag])=1,180000+_previousmove,800000)),
 IF(MAX('Table'[Date])>_mindate,
    IF(_previousflag=0,
    150000,
    IF(_previousflag=1,
    var _date1=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[flag]=0))
    var _sum1=SUMX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Date]>_date1&&'Table'[flag]=1),'Table'[Movement])+150000
    var _date=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),_sum1>800000))
    var _sum2=CALCULATE(SUM('Table'[Movement]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[Date]>_mindate&&'Table'[Movement]>0))
    Return
    IF(_previousmove>0,
     IF(MAX('Table'[flag2])=1,
       IF(800000+_sum3<150000,150000,IF(800000+_sum3>=150000&&800000+_sum3<800000,800000+_sum3,800000)),
       IF(_sum1+_sum4>150000&&_sum1+_sum4<800000,
        _sum1,800000)),
  IF(_previousmove<0,
          IF(_sum2<800000,_sum1,800000+_sum3)))))))

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

    • nirrobi's avatar
      nirrobi
      Helper V
      5 years ago

      Thank you so much!!!!!!!!

      I amended 210107 to -700000

      pls note 210109 - should be 150'000 - 100000-100000 <150000 --> 150000

      also 210110 is not correct

       

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        Hi  nirrobi ,

         

        Sorry,I dont think there is a suitable measure to fit different values based on such criteria,as the result has always been checked.Even I give a measure for this set of movements,it may return errors in another set of movements,as the results will be reset for many times,which cant be determined.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my post as a solution!