12 Month Moving Average of Measure with filter

[chris_k]

Hello,

I am calculating a 12 month Rolling Average for some data. The data has both an "entity" and an "approved" (date) column.

What I need to do is filter this measure by the "entity" which works perfectly currently but when a date filter is applied to the report I need it to ignore the date filtering (i cant use the interactions as my filters are date and entity linked). I was thinking something along the lines of using the ALL function? Any advice?

Measure so far:

12m Rolling Average = 
VAR __LAST_DATE =
   ENDOFMONTH('data_source_one'[approved].[Date])

VAR __DATE_PERIOD =
   DATESBETWEEN(
      'data_source_one'[approved].[Date],
      STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
      __LAST_DATE
   )

RETURN
   AVERAGEX(
      CALCULATETABLE(
         SUMMARIZE(
            VALUES('data_source_one'),
            'data_source_one'[approved].[Year],
            'data_source_one'[approved].[QuarterNo],
            'data_source_one'[approved].[Quarter],
            'data_source_one'[approved].[MonthNo],
            'data_source_one'[approved].[Month]
         ),
         __DATE_PERIOD
      ),
      CALCULATE([Data To Date], ALL('data_source_one'[approved].[Day]))
   )

[v-frfei-msft]

Hi @chris_k,

Maybe you can sue the ALLEXCEPT to work on it.

ALLEXCEPT(your table, youtable[date])

Also kindly share your sample data and excepted result to me.

Regards,

Frank

[chris_k]

Sample data:

entity	approved	Data To Date
51125	2018-09-01 00:00:00	0.00
253564	2016-01-01 00:00:00	NaN
253564	2016-02-01 00:00:00	0.00
253564	2016-03-01 00:00:00	0.00
253564	2016-04-01 00:00:00	0.00
253564	2016-05-01 00:00:00	22.73
253564	2016-06-01 00:00:00	0.00
253564	2016-07-01 00:00:00	0.00
253564	2016-08-01 00:00:00	0.00
253564	2016-09-01 00:00:00	0.00
253564	2016-10-01 00:00:00	0.00
253564	2016-11-01 00:00:00	0.00
253564	2016-12-01 00:00:00	0.00
253564	2017-01-01 00:00:00	0.00
253564	2017-02-01 00:00:00	0.00
253564	2017-03-01 00:00:00	5.61
253564	2017-04-01 00:00:00	0.00
253564	2017-05-01 00:00:00	0.00
253564	2017-06-01 00:00:00	0.00
253564	2017-07-01 00:00:00	0.00
253564	2017-08-01 00:00:00	0.00
253564	2017-09-01 00:00:00	0.00
253564	2017-10-01 00:00:00	0.00
253564	2017-11-01 00:00:00	0.00
253564	2017-12-01 00:00:00	0.00
253564	2018-01-01 00:00:00	0.00
253564	2018-02-01 00:00:00	2.19
253564	2018-03-01 00:00:00	1.85
253564	2018-04-01 00:00:00	0.00
253564	2018-05-01 00:00:00	0.00
253564	2018-06-01 00:00:00	0.00
253564	2018-07-01 00:00:00	0.00
253564	2018-08-01 00:00:00	0.00
253564	2018-09-01 00:00:00	0.00

Expected result:

Ability to filter by "entity" to give the 12m rolling average but ignore all filters applied to "approved" column. (Without editing the interactions).

So for 253564 result = 0.27

When looking at all data result = 0.18

Thanks,

[Chihiro]

Try using ALLEXCEPT(). Or ALL(),VALUES() combo.

Note that you should add columns not to be ignored in the function.

You can find more detailed explanation in link.

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/