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

Calendar Table Relationship Help

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

0 REPLIES 0

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.