cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Calculate average per month for a whole period

I have an INCIDENTS table that has an IncidentId and a DateTime.

 

 

 

DateTime,IncidentId
8/1/2018 00:00,Inc01
8/4/2018 00:00,Inc02
8/7/2018 00:00,Inc03
6/7/2019 15:42,Inc04
6/18/2019 00:00,Inc05
7/6/2019 14:55,Inc06
7/22/2019 00:00,Inc07
7/22/2019 00:00,Inc08
7/31/2019 00:00,Inc09
8/5/2019 00:00,Inc10
8/6/2019 00:00,Inc11
11/25/2019 19:38,Inc12
12/2/2019 10:46,Inc13
12/27/2019 08:48,Inc14
1/5/2020 19:00,Inc15
1/18/2020 14:03,Inc16
1/19/2020 13:54,Inc17
1/20/2020 13:56,Inc18

 

 

 

Two things to consider:

  1. There may not be an incident for every month between the first and the last dates.
  2. There may be duplicate DateTime entries (down to the second).

 

I want to calculate 'Average Incidents per Month', per context. But I want the 'number of months' to be the total number of months in that context regardless of there are incidents for each month in that context.

 

For instance, if you look at my data, there are 3 instances for the year 2018. And the date of the first incident in 2018 is August 1, 2018. So, my expected calculation should be

 

 

 

AvgIncidents/Month = 3/4 = 0.75

 

 

 

  • And similarly, since my last incident is on 2020, I should calculate 12 months for year 2019.
  • For year 2020, my last incident is on January, so I can only count 1 month for 2020.
  • If we consider the entire table, my number of months should be 4+12+1=17.

 

Essentially, I guess I'm trying to figure out how to get the number of months between two dates, constrained by the very firs and very last record of my table. Is that possible and how do I do that?

 

EDIT:

I did come up with a Measure like this which, I guess, gets the job done, but it seems to me a bit ugly. Is there a better way?

 

DiffMonths = 
	DATEDIFF(
		FIRSTNONBLANK(Incidents[Date/Time], 1=1),
		LASTNONBLANK(Incidents[Date/Time],1=1),
		MONTH)

 

1 REPLY 1
Highlighted
Super User II
Super User II

You will want to embrace the concept of a Calendar/Dates table. That will help you tremendously, not just with the "dry" periods

 

Another, related concept you will want to work with is the idea of a Left Join - and the importance of deciding which column from which table in your data model you want to place into your visuals in which order.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors