Calculating a Column and then Applying a Moving Average to it

[ryan_citywire]

Hi,

I'm fairly new to Power BI and DAX but have extensive BI experience. 

I have a table that stores raw transactional data, I'm looking to calculate the distinct number of users on a given day and then calculate the 7 day average of that. The raw data looks like this:

Transaction ID (PK)	User ID (FK)	DateTime	Date	Data ID (FK)	Component ID (FK)
173684030	-999	2017-12-21 00:00:30	2017-12-21	88238
173559419	2271046	2017-12-21 00:00:54	2017-12-21	192988
173685327	-999	2017-12-22 00:00:40	2017-12-22	1484961	946719
173556882	2271046	2017-12-22 00:01:01	2017-12-22	1485135	946687
173580215	-999	2017-12-22 00:01:37	2017-12-22	672582	777398
173642423	-999	2017-12-22 00:01:30	2017-12-22	844624	280462
173597374	2410429	2017-12-22 00:01:27	2017-12-22	909727	757739
173629767	2247960	2017-12-22 00:01:24	2017-12-22	1482228	945949
173581313	-999	2017-12-22 00:01:24	2017-12-22	1482241	946279

I would then have a table on my Power BI Dashboard that would show the following:

Date	Distinct Users	Rolling 7 Day Average Distinct Users
21/12/2017	2	2
22/12/2017	4	3

Obvioulsy making slightly more sense with more data going into it. As noted in the first table there are other tables loaded to the data mdoel that are related to my main transactional data table and therefore my summary table should update should any filteres be applied to either the main transactions table or any of the related dimension tables.

I have got nearly all the way there with a couple of approaches such as The Standard Moving Average methodology but this always sums up the Distinct Users for all of the rows in the tranx table and then divides by the total rows for the average calculation which is not what I want. I've also got some of the way there with a Summarize table but this doesn't interact with the rest of the filters as I would expect. 

Any help you could offer would be really appreciated.

Thanks,

Ryan

[Greg_Deckler]

Perhaps something like:

Distinct = DISTINCTCOUNT(aTransactions[User ID (FK)])

Rolling 7 Day Average = 
VAR DistinctUsers = SUMMARIZE(ALL(aTransactions),aTransactions[Date],"DistinctUsers",[Distinct],"MyDate",aTransactions[Date])
RETURN AVERAGEX(FILTER(DistinctUsers,[MyDate]>=MAX(aTransactions[Date])-7 && [MyDate]<=MAX(aTransactions[Date])),[DistinctUsers])

[ryan_citywire]

Thanks so much for your help, that nailed it . . . almost!

Because of the summarise being used for the Distinct Users - when I make a selection on a related table the figures aren't updated to reflect the selection / filter. Is there a way to include this functionality?

[ryan_citywire · Accepted solution]

7 Day Rolling Distinct Users = 
	AVERAGEX(
			DATESBETWEEN(	'aTransactions'[Date].Date
						,	DATEADD(LASTDATE('aTransactons'[Date]), -6, DAY)
						,	LASTDATE('aTransactions'[Date])
						)
		,	CALCULATE(DISTINCTCOUNT('aTransactions'[User ID (FK)]))
			)

This got me 99% of the way there in the end