Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChuckChuck
Helper I
Helper I

Repeat Values Forward In Time For Previously Calculated Variances

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!!

 

MonthCustomerMaterialResaleResale Change From Prior MonthResale Change From Prior Month  Better
MarchABC123$0.50  
AprilABC123$0.50$0.00$0.00
MayABC123$0.40($0.10)($0.10)
JuneABC123$0.40$0.00($0.10)
JulyABC123$0.40$0.00($0.10)
MarchXYZ789$0.90  
AprilXYZ789$0.90$0.00$0.00
MayXYZ789$1.25$0.35$0.35
JuneXYZ789$1.25$0.00$0.35
JulyXYZ789$1.25$0.00$0.35
JulyXYZ789$1.05($0.20)($0.20)
1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

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])

 

q1.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-qiuyu-msft
Community Support
Community Support

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])

 

q1.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CahabaData
Memorable Member
Memorable Member

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.....

www.CahabaData.com

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. 


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

www.CahabaData.com

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.