cancel
Showing results for
Did you mean:
Highlighted
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) 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.

Thanks,

Ryan

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

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

```7 Day Rolling Distinct Users =
AVERAGEX(
DATESBETWEEN(	'aTransactions'[Date].Date
,	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

## 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

Proud to be a Datanaut!

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?

Frequent Visitor

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

```7 Day Rolling Distinct Users =
AVERAGEX(
DATESBETWEEN(	'aTransactions'[Date].Date
,	LASTDATE('aTransactions'[Date])
)
,	CALCULATE(DISTINCTCOUNT('aTransactions'[User ID (FK)]))
)```

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

Announcements

#### Community Highlights

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

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 160 members 2,102 guests
Recent signins: