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
nirrobi's avatar
nirrobi
Helper V
5 years ago

many thanks for your help!! much much appriciated!

 

in the file you sent it seems to work correctly.

I add few more lines to the row data but in 202110 the result is not correct - should be 250000 (1500000+100000)

 

 

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

Hi  nirrobi ,

 

Modify the measure as below:

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(_sum1>150000&&_sum1<800000,_sum1,800000)))))

 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

    You are the best!!! 🙂

     

    but (:-() -  I face a problem in the below situation

     

    in 202103 - the amount should be 150000 (150000-100000 is less the minimus 00> 150000)

    in 202108 - the amount should be 300000 ( 800000-500000 --> 300000)

    • 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?