Calendar Table Relationship Help

[matthew_hampton]

Good Afternoon:

Looking for some help or best practice here.

Here is my data structure:

ID	Model	S/N	EffectiveFrom	EffectiveTo
1	ABC123	12345	1-1-2017	2-27-2107
1	ABC456	456789	2-28-2017	12-31-999
2	XYZ890	138901	1-3-2017	3-31-2018
3	QWE876	098781	2-1-2017	2-17-2017

What I want to be able to do as an example is to count how many records were active on a given day using the effective dating. Said another way Count where MM-DD-YYYY is between EffectiveFrom and EffectiveTo.

I created a calendar table in an attempt to do this using this formula:

Gateway = CALCULATE(COUNT(DailyDeviceReport[deviceIdentity]),FILTER('Calendar','Calendar'[Date]>=MIN(DailyDeviceReport[effectiveFromDate])&&'Calendar'[Date]<=MAX(DailyDeviceReport[effectiveToDate])))

The issue I am running into is the relationship between the calendar table and the DailyDeviceReport table. Since there is not a single date field that identifies a reocrd to a date I am stuck.

Any help is appreciated.

Matt