cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ryan_citywire Frequent Visitor
Frequent Visitor

Calculating a Column and then Applying a Moving Average to it

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)DateTimeDateData ID (FK)Component ID (FK)
173684030-9992017-12-21 00:00:302017-12-2188238 
17355941922710462017-12-21 00:00:542017-12-21192988 
173685327-9992017-12-22 00:00:402017-12-221484961946719
17355688222710462017-12-22 00:01:012017-12-221485135946687
173580215-9992017-12-22 00:01:372017-12-22672582777398
173642423-9992017-12-22 00:01:302017-12-22844624280462
17359737424104292017-12-22 00:01:272017-12-22909727757739
17362976722479602017-12-22 00:01:242017-12-221482228945949
173581313-9992017-12-22 00:01:242017-12-221482241946279

 

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

 

DateDistinct UsersRolling 7 Day Average Distinct Users
21/12/201722
22/12/201743

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
ryan_citywire Frequent Visitor
Frequent Visitor

Re: Calculating a Column and then Applying a Moving Average to it

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

3 REPLIES 3
Super User
Super User

Re: Calculating a Column and then Applying a Moving Average to it

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])

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ryan_citywire Frequent Visitor
Frequent Visitor

Re: Calculating a Column and then Applying a Moving Average to it

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 Frequent Visitor
Frequent Visitor

Re: Calculating a Column and then Applying a Moving Average to it

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 160 members 2,102 guests
Please welcome our newest community members: