cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirrobi
Helper V
Helper V

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
14 REPLIES 14
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1616575530778.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

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)

 

nirrobi_0-1616576832827.png

 

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:

v-kelly-msft_0-1616654159865.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

You are the best!!! 🙂

 

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

 

nirrobi_1-1616655119559.png

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)

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:

v-kelly-msft_0-1616722748112.png

For the updated .pbix file,pls see attached.

 

Best Regards,
Kelly

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

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?

 

 

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:

v-kelly-msft_0-1617095533102.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

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

 

nirrobi_0-1617185224238.png

 

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!

 

 

also 202109 is not correct - shoud be 800000 (800000+100000>800000 --> 800000)

selimovd
Super User
Super User

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

many 10x!!!

it will be highly appriciated

 

Nir

amitchandak
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

thanks for your reply 

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors