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 only

 

hence - the secind line is 150,000 because of 180-405 = -225 and it's less the minumus hence the result should be 150K

and so on - see details in the table below

 

how can I capture the previous line (date) I calcualte 1 mil second before?

I can use calculated column or measure but need to find solution to this problem (in excel it's very easy :-))

 

Many thanks!

 

 

 

DateMovementOpenExpected ResultLogic
202101-405451180000180000 
202102-64036 150000180-405=-225 < 150 --> 150
202103237137 150000150-64=85<150 -->150
202104110441 387137150+237=387>150 & 387<800 -->387
202005500000 800000387+500=887>800 -->800
Need Help

14 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey nirrobi ,

     

    the problem is that there is no recursive function that you would be helpful for that issue.

    However, I think it's possible to solve that with some iterators.

     

    I have a little time in a few hours, I will take a look then and give you feedback.

     

    Best regards

    Denis

    • nirrobi's avatar
      nirrobi
      Helper V
      5 years ago

      many 10x!!!

      it will be highly appriciated

       

      Nir

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

    Hi  nirrobi ,

     

    Per your description,for date 202104,you are comparing values from the previous movement add 150 with 150,but for the last row ,you are comparing values from the current movement add 150,need it to modify as 387+110<800,so it should return as 497578?

    If so,check below:

    First,create a calculated column:

    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,
IF('Table'[Movement]+150000<150000,0,
IF('Table'[Movement]+150000>150000&&'Table'[Movement]+150000<800000,1,2))))

    Then create a 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 _sum=SUMX(FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])&&'Table'[flag]=1),'Table'[Movement])+150000
    Return
   IF(_sum>150000&&_sum<800000,_sum,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

      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!

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    nirrobi , When I paste these number s on excel. I am not geting 405 , 64 and 110 as separate number.

     

    Date

    		 Movement Open Expected Result Logic
    202101 -405451 180000 180000  
    202102 -64036   150000 180-405=-225 < 150 --> 150
    202103 237137   150000 150-64=85<150 -->150
    202104 110441   387137 150+237=387>150 & 387<800 -->387
    202005 500000   800000 387+500=887>800 -->800

     

    Can you provide better sample

    • nirrobi's avatar
      nirrobi
      Helper V
      5 years ago

      thanks for your reply 

      the 405 should read 405 K = 405000 = 180000-405451=-225451