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...
BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

Hi There,

 

Your description of the problem is thorough but need a sample data nd Expected output to understand the problem practically.

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