Forum Discussion

ChuckChuck's avatar
ChuckChuck
Helper I
8 years ago
Solved

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...
Need Help
Show and Tell
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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's avatar
ChuckChuck
Helper I
8 years ago

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's avatar
CahabaData
Memorable Member
8 years ago

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's avatar
    ChuckChuck
    Helper I
    8 years ago

    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?