New Column Doesn't Inherit Top-Level Filters

[corodyne]

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 script in DAX (as much as I have been able to learn it so far).

Secondly, I'm having trouble getting Power BI to do what I think it should be doing, especially as I am trying to convert a client's excel spreadsheet model into a Power BI dashboard that allows some filters (aka slicers).

Question Up Front:  Do the top-level filters/slicers get passed to Columns or is there some specific way to reference the page-level filters from within the code below?

Many thanks in advance for your thoughts.

Here's the idea:

My client distributes medicine.  However, they want to be able to tracking rolling demand by downstream elements.  So, they want to be able to filter by the downstream vendor and medicine type (these are the slicers at the top level).

Here's the problem:

The filters don't seem to get passed to the column that I am trying to populate.  (code below)  When I enter data that is PRE-filtered from excel, the calculations and dashboard appear to display the numbers I would expect, meaning there is only one downstream vendor and one medicine type.

HOWEVER, when I try to run this code in the larger system, it goes haywire and isn't filtering as I thought it should.  I added some subsequent filters in the code below to limit only on the medicine type and downstream vendor and found that THOSE filters worked.  That told me the page-level filters were not making it into the code below when used within the larger data set.

RollingSumDemand = 
// set the variables to be used as the activity date and Average Days of Supply based on demand
VAR RowDate = Sheet1[activity_date]
VAR AverageDaysSupply = ROUNDUP(sum(Sheet1[Vials_On_Hand])/sum(Sheet1[Vials_Transfer_Out]),0)

RETURN
    CALCULATE (
		// add up the total lost sales and the total vials sold
        SUM ( Sheet1[Vials_Lost_Sales] ) + SUM ( Sheet1[Vials_Sold] ),
		// as bounded by "today" - which is the RowDate during the iterative process (part 1 before && below)
		// and "today" minus the AverageDaysSupply (part 2 after && below)
		FILTER (
            Sheet1,
            Sheet1[activity_date] > RowDate-AverageDaysSupply && Sheet1[activity_date] <= RowDate
        )
	)

[BhaveshPatel]

Hi There,

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

[corodyne]

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]

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]

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!