Rolling average (fixed window) of a cumulative total (all time)

[DBDave]

Hi. I've got a fact table which holds the dates that a business activated a product and the date that the business cancelled that product (if at all). There's also a Calendar/date table with relationships set up between each of these dates and the Date-key column in this Calendar table. There are also some tables off of the Calendar table purely to aid in setting up visual filters/slicers.

 Using this I've created measures for the number of activations and cancellations, using the following DAX:

Activations = 
CALCULATE(
	COUNT(Businesses[Business ID]), 
	ISBLANK(Businesses[Date Activated]) = FALSE(),
	USERELATIONSHIP(Businesses[Date Activated],'Calendar'[Date])
	)

and

Cancellations = 
CALCULATE(
	COUNT(Businesses[Business ID]), 
	ISBLANK(Businesses[Date Cancelled]) = FALSE(),
	USERELATIONSHIP(Businesses[Date Cancelled],'Calendar'[Date])
	)

I now need to add a measure for a rolling 30-day average of the cumulative count of each of these measures. Since they're similar, I'll show what I've done for just the Activations.

I created a measure for the cumulative count easily enough:

Activations (Cumulative Count) = 
CALCULATE(
	COUNT(Businesses[Business ID]),
	FILTER (
            ALL('Date Selector'),
			'Date Selector'[Date ID] <= MAX( 'Date Selector'[Date ID] )
        	),
	ISBLANK(Businesses[Date Activated]) = FALSE(),
	USERELATIONSHIP(Businesses[Date Activated], 'Calendar'[Date])
)

So I now have something like this:

Next, I added a measure for an average of this cumulative count:

Activations (Moving Avg of Cumulative Count) = 
AVERAGEX (
    DATESINPERIOD (
        'Calendar'[Date],
        LASTDATE ( 'Calendar'[Date] ),
        -30,
        DAY
    ),
    [Activations (Cumulative Count)]
)

Which kinda works, but the problem I have now is that it doesn't honour the date selectors I filter by. I.e. it is always calculating the average from the beginning of the period displayed, not from the beginning of time. For example, in the screenshot below I've filtered to show only the previous 30 days, and you can see the 1st value of the average equals the first value of the cumulative total:

Is there some way I can force it to ignore the filter and calculate this average from the earliest date in the Calendar table?

Thanks,

Dave

[Anonymous]

Hi @DBDave,

>>Is there some way I can force it to ignore the filter and calculate this average from the earliest date in the Calendar table?

I test a lot but I found it seem impossible to ignore original filter through outside filter. Maybe you need to write a new measure to work on it.

Regards,

Xiaoxin Sheng

[DBDave]

I'm not sure what you mean by this sorry; "Maybe you need to write a new measure to work on it".  Can you explain?  

Are you saying what I want to get (a rolling avg of a cumulative total measure) is not possible?  I find that hard to believe.  The demo file I created can still be found here: https://1drv.ms/u/s!AmKJPcPpoTDSh600gJGvEAkgBgylNA if anyone else feels like having a crack at it.  

If you open the PBIX file you'll notice that the moving avg of cumulative count (right-most column) is correct, for the date range displayed. But I need that moving average to be from the beginning of time, i.e. to ignore any date filters applied to the table.

 Thanks,

Dave

[Anonymous]

Hi @DBDave,

>>Are you saying what I want to get (a rolling avg of a cumulative total measure) is not possible?

It is impossible to use outside filter to ignore the origianl fiter which write in the measure.
(for e.g, measure1 has a filter with static date range, I want to use calculate function with outside filter to ignore the original filter)

>>I'm not sure what you mean by this sorry; "Maybe you need to write a new measure to work on it".  Can you explain?  

It means we need to write a new measure to add a condition to check if current row is the filtered first row, then we can add some expand calculation on it.

For example:

If(MAX([Date])=FIRSTDATE(ALLSELECT(Table[Date]),new formula(used to break current filter and calculate with other records),original formula)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @DBDave,

Did you try to use "allselect" filter on calendar table? For example:

Activations (Moving Avg of Cumulative Count) = 
AVERAGEX (
    DATESINPERIOD (
        ALLSELECTED('Calendar'[Date]),
        LASTDATE ( 'Calendar'[Date] ),
        -30,
        DAY
    ),
    [Activations (Cumulative Count)]
)

In addition, it will be help if you share some sample data to test.

Regards,

Xiaoxin Sheng

[DBDave]

Hi. Thanks for the suggestion, but I then get the error "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument"

I can actually share the whole PBIX file itself - it's all just dummy data for the purposes of solving this issue anyway. 

https://1drv.ms/u/s!AmKJPcPpoTDSh600gJGvEAkgBgylNA

Thanks

Dave