cancel
Showing results for
Did you mean:
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!

 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
14 REPLIES 14
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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Helper V

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)

Community Support

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

Helper V

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)

Community Support

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

Helper V

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?

Community Support

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

Helper V

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

Community Support

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

Helper V

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

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

Helper V

many 10x!!!

it will be highly appriciated

Nir

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

Helper V

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors