Correcting a rolling average measure with a date slicer

[TTNK]

Consider a simple table consisting of date and fact.

On this table I want to compute the a 30-day rolling average. Common wisdom on this forum and elsewhere is to compute the rolling average as follows:

Rolling average := IF(
	ISFILTERED('Table1'[Timestamp].[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = LASTDATE('Table1'[Timestamp].[Date])
	VAR roll = 
		AVERAGEX(
			DATESBETWEEN(
				'Table1'[Timestamp].[Date],
				DATEADD(__LAST_DATE, -30, DAY),
				__LAST_DATE
			),
			CALCULATE([fact])
		)
RETURN IF(__LAST_DATE > TODAY(), BLANK(), roll)
)

Which works fine. The problem comes when I try to filter the data using a slicer. Note the difference in the values of the rolling average when the starting point for the slicer is pushed one day backward:

Note that the fact is not changing from date to date, but the rolling average measure is changing. Desired behavior is that the rolling average measure use the previous 30 days of fact, not the days of fact available after filtering. E.g. what happens to the measure at June 1 and onward when I extend the slicer 30+ days before June 1.

These are correct values for the 30-day rolling average and the behavior I expect.

I've tried solutions based on ALL() and ALLSELECTED() but I'm not getting anywhere. My understanding of filters and filter contexts is very rudimentary, so if you could provide a short explanation along with a working code, that would be awesome.

Thanks in advance.

[Anonymous]

Hi @TTNK,

Maybe you can try to use below formula, I modify your formula to add 'allselected' function to enable filter effects from other visual.

Rolling average =
VAR _lastDate =
    MAX ( 'Table1'[Timestamp] )
VAR roll =
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'Table1' ),
            [Date]
                >= DATE ( YEAR ( _lastDate ), MONTH ( _lastDate ) - 1, DAY ( _lastDate ) )
                && [Date] <= _lastDate
        ),
        CALCULATE ( [fact] )
    )
RETURN
    IF ( _lastDate > TODAY (), BLANK (), roll )

If above not help, can you please share some sample data for test?

Regards,

Xiaoxin Sheng

[Anonymous]

HI @TTNK,

Actually, I'm not so recommend to use 'Time Intelligence functions'. It looks smart and easy to use, but it is hard to know internal calculation logic and nested use multiple functions.

In my opinion, I'd like to suggest you take a look at following links which told about how to manually achieve similar filter effects and do calculation on them.

Time Intelligence "The Hard Way" (TITHW)

Regards,

Xiaoxin Sheng

[TTNK]

---

@Anonymous wrote:

In my opinion, I'd like to suggest you take a look at following links which told about how to manually achieve similar filter effects and do calculation on them.

Time Intelligence "The Hard Way" (TITHW)

---

Hi @Anonymous

Thanks for the reply. I've downloaded the pbix example for "The Hard Way" time-intelligence and after examining it, I can see no difference between the results obtained by The Hard Way vs. built-in TI. Am I missing something?

[LivioLanzo]

@TTNK

Does your model contain a Date dimension table and a fact table or do you have everything in one denormalized table?

Time intelligence formulas only work properly when you have a Date dimension marked as Date table 

[TTNK]

I have these particular data in one denormalized table. 

---

@LivioLanzo wrote:

Time intelligence formulas only work properly when you have a Date dimension marked as Date table 

---

How exactly does one do this?