Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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. )
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |