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