Repeat Values Forward In Time For Previously Calculated Variances

[ChuckChuck]

I'm really struggling with a new requirement for displaying changes in resale. I currently have calculations working just fine to compare a customer and material combination against the prior month's activity and displaying the change between the periods. What I need now is to go beyond that and replicate what you see in the last column. How can I create this logic in Power BI? What I need to do is continue to obtain the resale increase/decrease, but if the next month after the variance is the same as the month with the variance (so zero actual change), I need to copy the variance down from the prior month. The reasoning behind this is that a resale drop is signifing a profit leak and that leak will continue in the subsequent months, so I need to see the value in those subsequent months. My table has ~4.8M records and everything I've tried so far causes Power BI to lockup and my computer to become unresponsive. Surely there's a way I'm not finding to make this happen. In Excel I'd just write an if statement and grab the value from the row above if true...I just can't figure this out in Power BI (I'm still pretty new to the tool). Thank you!!

Month	Customer	Material	Resale	Resale Change From Prior Month	Resale Change From Prior Month  Better
March	ABC	123	$0.50
April	ABC	123	$0.50	$0.00	$0.00
May	ABC	123	$0.40	($0.10)	($0.10)
June	ABC	123	$0.40	$0.00	($0.10)
July	ABC	123	$0.40	$0.00	($0.10)
March	XYZ	789	$0.90
April	XYZ	789	$0.90	$0.00	$0.00
May	XYZ	789	$1.25	$0.35	$0.35
June	XYZ	789	$1.25	$0.00	$0.35
July	XYZ	789	$1.25	$0.00	$0.35
July	XYZ	789	$1.05	($0.20)	($0.20)

[v-qiuyu-msft · Accepted solution]

Hi @ChuckChuck,

In your scenario, you can create a index column(from 1) in Query Editor. Then create calculated columns below: 

pre = CALCULATE(MAX('Table1'[Resale]),FILTER('Table1','Table1'[Customer]=EARLIER(Table1[Customer]) && 'Table1'[Material]=EARLIER('Table1'[Material]) && 'Table1'[Index]=EARLIER(Table1[Index])-1 ))

diff = IF('Table1'[pre]=BLANK(),BLANK(),'Table1'[Resale]-Table1[pre])

IndexNew =
var filterd=FILTER('Table1',[Customer]=EARLIER([Customer]) &&[Material]=EARLIER([Material]))
var nonblank=MAXX(FILTER(filterd,[Index]<=EARLIER([Index])&&[diff]<>0),[Index])
return nonblank

Resale Change From Prior Month  Better = LOOKUPVALUE('Table1'[diff],'Table1'[Index],'Table1'[IndexNew])

Best Regards,
Qiuyun Yu 

[ChuckChuck]

WOW! You're amazing, @v-qiuyu-msft! Thank you!! 

[v-qiuyu-msft]

Hi @ChuckChuck,

If you are satisfied with my reply, would you please mark it as an answer so we can close this thread? 

Best Regards,
Qiuyun Yu 

[CahabaData]

for brevity will refer to your columns as RC1 and RCbetter.  I think your post is saying you have a measure RC1 that is working - but then trying to include measure RCbetter is causing the lock up.

I will hazard the guess is due to iterating by row on 5M rows, twice, is the cause.  But if it always locking up then you can't be 100% sure your RCbetter formula is correct.  So if you are able to experiment on a smaller data set - it would be a good sanity check to verify that point.

If RCbetter is correct - then I wonder if one must make RC1 a calculated column.  Generally the advice is against that approach.  But everything is a trade off of memory v processing.  Possibly a more efficient RCBetter dax statement is a solution without going the calculate column approach - you would need to post that for community to see. 

Of course the amount of RAM in your machine is relevant too.....

[ChuckChuck]

My machine is a laptop, but even so it has 16 gigs of ram. The comparison I'm making to obtain the resale from the prior period (really the variance from current to prior) is I have a summerize table that stores the resale value for each unique combination of customer and material number. In my main table I then do a lookup to the earlier period to get the value of the prior resale and from there perform the calculation. 

Now I need to figure out how to take that variance (plus or minus) and repeat it for the periods following the variance when those future periods remain unchanged from that initial variance.

I've tried with a smaller sample size, but any formula I try either flat out doesn't work or give a circular reference error. 

[CahabaData]

Please clarify if your first post represents the actual table data set; if not it is best to provide that sample(s) to be clear as to what RCbetter is working with.

I

[ChuckChuck]

Yes, this is dataset I'm working with. I have a calculated column to join the customer and material together to form a unique key. To get the comparision against the prior period, I'm doing a lookupvalue formula to look at a summarized table and I'm grabbing the resale from the prior period, then doing some basic math to determine if the change when up or down, or no change at all. The task now is to figure out how to pass the prior change value down to the subsequent periods, since each subsequent period could be impacted by profit leak if the sales value had dropped. Any ideas?