Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
lbendlin
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors