Re: Running Total dependency based on previous dat...

nirrobi · ‎03-21-2021

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

v-kelly-msft · ‎03-24-2021

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 · ‎03-24-2021

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 · ‎03-24-2021

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 · ‎03-24-2021

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 · ‎03-25-2021

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 · ‎03-25-2021

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 · ‎03-30-2021

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 · ‎03-31-2021

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 · ‎03-31-2021

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!

nirrobi · ‎03-24-2021

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

selimovd · ‎03-22-2021

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 · ‎03-22-2021

many 10x!!!

it will be highly appriciated

Nir

amitchandak · ‎03-21-2021

@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

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

nirrobi · ‎03-21-2021

thanks for your reply

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

Running Total dependency based on previous date

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly