Forum Discussion

corodyne's avatar
corodyne
Frequent Visitor
9 years ago

New Column Doesn't Inherit Top-Level Filters

First off, please let me say thanks to all of you that have been posting solutions and helping folks on this forum.  I have read your posts with thanks as they have been helping me learn how to scrip...
corodyne's avatar
corodyne
Frequent Visitor
9 years ago

Bhavesh,

 

Thanks for your willingness to look at this.

 

I've created a sample spreadsheet with two tabs.  One has all the sample data for three distribution centers and products that flow through each one.  The second tab has what I need to be able to represent as a rolling sum.

 

Assuming my average inventory will last eight days, I need to have a rolling sum calculated for the distribution center/product combination and rendered/stored  (the last column on the right in tab 2).

 

I've tried very hard to solve this on my own, just coming up short as I don't have enough experience with DAX and Power BI.

 

Data Link in Google Drive:

 

Example Data and Expected Output

 

Thanks again for your offer to help.

 

Best,

 

Chris

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi corodyne,

 

Based on my understanding, you want to get the rolling  total which has the same vendor, medicine type within 8 days, right?

 

If it is a case, you can refer to below code to get the rolling total:

 

Measure(since I can’t find the columns of “Vials_On_Hand”,”Vials_Transfer_Out” I modify the formula to get the total of “qty sold”):

 

RollingTotal = 

var currDate=MAX(Table1[date])

var currentName=LASTNONBLANK(Table1[location name],Table1[location name]) 

var currProduct=LASTNONBLANK(Table1[product code],Table1[product code])

return

SUMX(FILTER(ALL(Table1),AND([date]>=currDate-8,[date]<=currDate)&&Table1[location name]=currentName&&Table1[product code]=currProduct),Table1[qty sold])

 

If above is not help, please provide more detail info about your data tables.

 

Regards,

Xiaoxin Sheng

  • corodyne's avatar
    corodyne
    Frequent Visitor
    9 years ago

    Xiaoxin,

     

    I think I may realize what the problem is now, more than anything.

    I have been trying to get the "calculated column" to update itself and recalculate each time the user makes a different selection in the dashboard slicer.  In reading a few things, I'm starting to sense that columns can't be remade on the fly - they are only recalculated when new data is added to the data source.

     

    This would be a huge blow to what we're trying to do in terms of graphing the data dynamically for our client.

     

    Thanks for your code, though, we may still use it!