Average of distinctcount over

[Anonymous]

Hi!

I'm trying to create an average of unique values (user ID) over a 12 month period.

Every user logs a couple of entries every month so to get the monthly value i do a distinctcount. 

Jan: 800

Feb: 900

Mar: 950

Apr: 1000

May: 1050

I want to return the average of these and be able to use it in a rolling twelve, so for the data above i expect:

(R3 to save space)

Mar: 883,3

Apr: 950

May: 1000

[v-shex-msft]

Hi @Anonymous,

You can follow below steps to create a calcualte column to show the dynamic average.

1. Convert Month column to date as index.

Date = DATEVALUE([Month]&"/1") 

2.  Use date column to calculate the rolling average.

Dynamic Average = AVERAGEX(FILTER(ALL('Table'),[Date]<=EARLIER('Table'[Date])),[Amount]) 

Regards,

Xiaoxin Sheng

[Anonymous]

Sorry I wasn't very clear on my data or case. 

I've got a date table that translates periods to date

and a records table where each emplyee i stamped on periods.  (400 000 rows, 3000 uniqe employees, 8 years of data)

timeaccperiod	Date
201731	2017-08-06
201732	2017-08-13
201733	2017-08-20
201734	2017-08-27
201735	2017-09-31
201736	2017-09-07
201737	2017-09-14
201738	2017-09-21
201739	2017-09-28

employee	timeaccperiod
AA	201731
BB	201731
AA	201732
BB	201732
AA	201733
BB	201733
AA	201734
AA	201735
AA	201736
AA	201737
AA	201738
AA	201739

Now i can do a distinctcount to get 

2017-08 = 2 (AA&BB)

2017-09 = 1 (AA)

But I can't get an rolling twelve average

I want to be able to plot it in a matrix/graph like this

jan-17	feb-17	mar-17	apr-17	maj-17	jun-17	jul-17	aug-17	sep-17
820	827	834	841	848	855	862	869	876

Where sept-17 is the average uniqe number of employees per month okt-16 to sept-17.

Durring any month there's 800-900 uniqe employees but since employees are coming and going the total unique employees over 12 months is more like 1200. 

(i then want to divide this by another value, but I don't think that's relevant. )

[Ashish_Mathur]

Hi,

Hoe did you get to those answers from the data that you shared.  Please share a complete example with the answer you are expecting on that dataset.

[CahabaData]

Because in your post you do not display a year value ; I just want to point out that this is needed and that your column is set as a date column, not text. 

So assuming your date column is Year Month date - here is some air code for a measure that should work for step 1 which is to establish the Year to Date sum:

YTD =

VAR

RowDate = CALCULATE( MAX( 'Table'[Yearmonth] ) )

RETURN    

  CALCULATE(  SUM ( 'Table'[ValueColumn] ),       

        FILTER(  ALLSELECTED( 'Table' ),           

          'Table'[Yearmonth] <= RowDate  && YEAR( 'Table'[Yearmonth] ) = YEAR( RowDate ) ) )

Once you have this YTD step 1, then you can create a new measure that divides the YTD measure by 12 - or modify the YTD measure itself to include that division also if you prefer.