Forum Discussion
Calculating Difference between Two Days based on a variable
Good Day I need assistance with adding a Calulcated Column to my Database. I need worked hours to take the SMR (Hour Meter) from the day it is recorded and subtract it from the SMR of that "s...
Hi Anonymous ,
You can use VAR to improve preformance.
Previous SMR = VAR _t = TOPN ( 1, FILTER ( DailyProduction, DailyProduction[EIID] = EARLIER ( DailyProduction[EIID] ) && DailyProduction[SMR] < EARLIER ( DailyProduction[SMR] ) ), [Date], DESC ) RETURN MAXX ( _t, [SMR] )Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, that formula doesn't seem to be working, it just generates "-1" in all the columns.
I have managed to come up with a formula that works. The only issue is that that data set is over 1m lines, and processing this column takes almost all my memory and 10 minutes to run.
Previous SMR =
var _EIID = DailyProduction[EIID]
var _SMR = DailyProduction[SMR]
return
CALCULATE(
MAX(DailyProduction[SMR]),
TOPN(1, FILTER(DailyProduction, DailyProduction[EIID] = _EIID && DailyProduction[SMR]<_SMR), DailyProduction[Date], DESC)
)
v-chenwuz-msft
Community Support
Community SupportHi Anonymous ,
You can use VAR to improve preformance.
Previous SMR =
VAR _t =
TOPN (
1,
FILTER (
DailyProduction,
DailyProduction[EIID] = EARLIER ( DailyProduction[EIID] )
&& DailyProduction[SMR] < EARLIER ( DailyProduction[SMR] )
),
[Date], DESC
)
RETURN
MAXX ( _t, [SMR] )
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a lot! It still takes a while to run but I think that is just the nature of the calculation I am doing. I am changing the actual input of the database to include this number already so that I don't have to calculate it via formula.
Thanks again