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
