Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have some data like this (here only shows the first 20 rows): This is raw data about each project year, each state, region, device has multiple events, and the value for each event is "Count" column.
Project_year | State | AreaName | LocalName | Device_ID | Count | Event_Date_Time |
2016 | STATE_A | Baseline | L.R. BOYLE PARK | D330 | 346 | 7/31/10 8:25 AM |
2016 | STATE_A | Baseline | L.R. HINDMAN | D930 | 909 | 8/23/09 6:40 AM |
2016 | STATE_A | Baseline | L.R. HINDMAN | D930 | 2055 | 1/12/13 1:54 PM |
2016 | STATE_A | Baseline | L.R. HINDMAN | D930 | 1194 | 7/3/17 3:48 AM |
2016 | STATE_A | Baseline | L.R. EAST | F340 | 194 | 9/27/09 10:44 AM |
2016 | STATE_A | Baseline | L.R. INDUSTRIAL | F520 | 79 | 12/19/09 9:19 AM |
2016 | STATE_A | Baseline | L.R. INDUSTRIAL | F520 | 73 | 11/22/10 10:24 PM |
2016 | STATE_A | Baseline | L.R. INDUSTRIAL | F520 | 73 | 5/30/17 1:48 AM |
2016 | STATE_A | Baseline | L.R. INDUSTRIAL | F520 | 73 | 11/3/17 4:32 PM |
2016 | STATE_A | Blytheville | ARMOREL | 1001C | 20 | 10/23/09 8:59 AM |
2016 | STATE_A | Blytheville | ARMOREL | 1001C | 182 | 8/21/10 9:48 PM |
2016 | STATE_A | Blytheville | ARMOREL | 1001C | 179 | 8/26/12 6:58 AM |
2016 | STATE_A | Blytheville | BLYTHEVILLE I-55 | 1042 | 212 | 4/7/15 11:35 AM |
2016 | STATE_A | Blytheville | BLYTHEVILLE I-55 | 1042 | 228 | 1/22/16 1:57 AM |
2016 | STATE_A | Blytheville | BLYTHEVILLE ELM ST. | 34 | 155 | 10/1/13 6:32 PM |
2016 | STATE_A | Blytheville | BLYTHEVILLE ELM ST. | 34 | 367 | 2/13/16 4:53 AM |
2016 | STATE_A | Harrisburg | NEWPORT | N446 | 1059 | 3/5/09 1:58 PM |
2016 | STATE_A | Harrisburg | NEWPORT | N446 | 1051 | 7/21/09 8:23 AM |
2016 | STATE_A | Harrisburg | NEWPORT | N446 | 400 | 8/7/18 7:29 PM |
2016 | STATE_A | Harrisburg | HARRISBURG | P809 | 1476 | 2/25/10 8:46 PM |
At the end of each month, we sum(count) the past 12 month then divided by 12 to get the 12 month rolling average, in some month there is no event happening thus there will be no sum(count), however we still want to include it in the calculation. Here are my two calculation:
Rolling12MonTotal = CALCULATE(SUM('SAMPLE DATA'[Count])
,CALCULATETABLE('Calendar',DATESBETWEEN('Calendar'[Date],EDATE(min('Calendar'[Date]),-11),MAX('Calendar'[Date]))
)
)
Rolling Average = if(ISBLANK(SUM('SAMPLE DATA'[Count])),BLANK(),divide('SAMPLE DATA'[Rolling12MonTotal], 12))
Rolling Average2 = divide('SAMPLE DATA'[Rolling12MonTotal], 12)
Question 1: As you can see, if I choose to graph "Rolling Average2" over date(year,month), then there is a problem that it plots the highlited 2019 all months data which are invalid, cos right now it is only 2019 Jan. If I choose to plot "Rolling Average", it is good to see all >2019Jan data points gone, but it creates new problem that some data points will be missing due to no data for that month, also those month data is not showing in tooltip. For example these red highlighted numbers are missing in powerBI:
Question 2: I would like to create a measure to show only the last month data, for example, right now is 2019-01-12, I would like to create sum(2018-1-1 to 2018-12-31) / 12, which the value is 507 (you can see this value from my above tooltip of graph "rolling average2"). How to create this measure?
The pbix file can be found here:
https://drive.google.com/open?id=10xVkLYQ4c2TOI2rJEMyvC78vfYq7gAtJ
Thank you very much!
Solved! Go to Solution.
Hi,
For Question1, try this rolling average measure
=if(ISBLANK(MIN('Calendar'[Date])),BLANK(),divide('SAMPLE DATA'[Rolling12MonTotal], 12))
Hope this helps.
Hi,
For Question1, try this rolling average measure
=if(ISBLANK(MIN('Calendar'[Date])),BLANK(),divide('SAMPLE DATA'[Rolling12MonTotal], 12))
Hope this helps.
Hi @Ashish_Mathur, thank you! It works for my question 1.
Any suggestion for question 2? Thanks!
You are welcome.
I believe the answer is in my file to get the average of the previous 12 months
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@LivioLanzo, I am sorry I checked your file a couple of times and the number is not right.
BTW I already solved my question 2. Thank you very much for both your input. @LivioLanzo @Ashish_Mathur
Hello @fyoung
I have uploaded a file for you here: https://1drv.ms/u/s!AiiWkkwHZChHj1SpwTfrfT-hkjXD
let me know if this is what you're after
I have applied a much needed star schema and then used this measure:
Rolling 12M Avg = IF( MAX( 'Calendar'[Month Year Sequence] ) > 12, IF( HASONEVALUE( 'Calendar'[Month Year Number] ), CALCULATE( DIVIDE( SUMX( SUMMARIZE( 'Calendar', 'Calendar'[Month Year Number], 'Calendar'[Month Year Name] ), CALCULATE( SUM( Data[Count] ) ) ), 12 ), FILTER( ALL( 'Calendar'[Month Year Sequence] ), AND( 'Calendar'[Month Year Sequence] < MAX( 'Calendar'[Month Year Sequence] ), 'Calendar'[Month Year Sequence] >= MAX( 'Calendar'[Month Year Sequence] ) - 12 ) ), ALL( 'Calendar' ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |