How can I stop pivot table filtering from breaking my 14d rolling average measure?

Question

I have two tables: FactTable and Calendar. My Fact table has consumption data for a variety of objects, and I want to create a 14 day rolling average measure. I have been somewhat successful, but I've noticed that whenever the consumption quantity for an object is zero, the rolling average displays zero for that day. The values that are not zero are correct, but I would like to have a continuous rolling average rather than having gaps.

Here is my Code:

=VAR Interval = -14
VAR FilteredDate = LASTDATE(FactTable[Date])        
VAR DateRange = DATESINPERIOD(
    'Calendar'[DateKey], FilteredDate, Interval, DAY)

VAR ConsumptionTotal = CALCULATE(
    SUM( FactTable[Consumption Qty] ),
    DateRange )

VAR DaysTotal = CALCULATE( 
    DISTINCTCOUNT('Calendar'[DateKey]), 
    DateRange)

RETURN

ConsumptionTotal / DaysTotal

What I think is happening is that the filtering from the pivot table for the object (1804 in the example above) is stopping the DateRange variable from properly filtering the FactTable into the dates within the range I want because when there is zero consumption quantity it does not return a full date range to SUM() with.

I've tried everything I can think of but whenever I test out ALL(), ALLSELECTED(), etc. to try to stop the filtering of the DateRange I get errors. I'm very new to DAX so I'm sure there something simple I'm missing. The scope of filters is very confusing to me.

@Aldert Unfortunately I can't give my raw data (health related) but I can give you samples of my model and tables: imgur.com/a/BubTDrI There's the three active tables (FactTable, Calendar, Index) and the model. Does this help add context? — LeroyBrown
@MurrayFoxcroft "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument." is the usual one when I try to wrap any of the date ranges in ALL() or ALLSELECTED(). — LeroyBrown

LeroyBrown LeroyBrown · Accepted Answer · 2020-09-18T17:58:47

Solved it.

=VAR Interval = -14
VAR FilteredDate = CALCULATE(
    LASTDATE(FactTable[Date]),
    ALL(Index),
    ISBLANK(FactTable[Consumption Qty]) = FALSE
    )
    
VAR DateRange = DATESINPERIOD(
    'Calendar'[DateKey], FilteredDate, Interval, DAY)

VAR ConsumptionTotal = CALCULATE(
    SUM( FactTable[Consumption Qty] ),
    DateRange )

VAR DaysTotal = CALCULATE( 
    DISTINCTCOUNT('Calendar'[DateKey]), 
    DateRange)

RETURN

ConsumptionTotal / DaysTotal

I learned a valuable lesson. The filter context was filtering in the Index table (which I didn't mention originally because I thought it wasn't important). That filter then propagated down to the FactTable where I was trying to remove filters. I appears that you need to remove the filtering at the original table being filtered or else you end up trying to work with already filtered values.

The change made was to the FilteredDate variable, where we add ALL(Index) to the CALCULATE() function to stop any filters from being applied to the Index table, which is where we end up getting the FactTable[VPC] column filtered when we don't want it to be. After that I was able to add in a second filter to the FilteredDate variable that ensures that only days with some consumption are counted in determining the first day to start each rolling average.

Looking back, I didn't do as good of a job as I should have in explaining the problem fully.

How can I stop pivot table filtering from breaking my 14d rolling average measure?

1 Answers