Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
18 | |
11 | |
5 | |
4 | |
3 |