March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
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!
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)
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!
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)
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!
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:
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
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)
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
@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
thanks for your reply
the 405 should read 405 K = 405000 = 180000-405451=-225451
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |