Using non related calendar table to get distinct count from two tables

[bikram_laishram]

Hello Folks,

Merry Xmas and Happy new year in advance. 

I am looking to build a report which will help me evaluate all breakdowns in a date range based on factors like builders, owner or primary managers. I have two tables one for holding the fleet information having a start date and and date. The other tables has work orders related information like equipment info and the last done date. the sample files are below for your perusal

Vessel_Id	Vessel_Name	Vessel_Fleet	Vessel_Type	Maker_Name	Ultimate_Owner	Primary_Manager	Primary_Manager_Valid_From	Primary_Manager_Valid_To
304782	Vessel 1	Dry	Container	< MAN B&W >	Owner 1	Company 1	9/26/2017	NULL
304781	Vessel 2	Tanker	Oil Tanker	Burmeister & Wain	Owner 2	Company 2	9/27/2017	NULL
304780	Vessel 3	Gas	Gas Tanker	Burmeister & Wain	Owner 3	Company 3	9/28/2017	NULL
304779	Vessel 4	Dry	Bulk Carrier	< MAN B&W >	Owner 4	Company 2	9/29/2017	NULL
304778	Vessel 5	Dry	Container	Burmeister & Wain	Owner 5	Company 2	9/29/2017	NULL
304777	Vessel 6	Dry	Bulk Carrier	< MAN B&W >	Owner 6	Company 2	9/29/2017	NULL
304776	Vessel 7	Dry	Bulk Carrier	MAN B&W	Owner 7	Company 6	10/31/2017	NULL
304775	Vessel 8	Dry	Container	< SULZER >	Owner 8	Company 2	11/1/2017	NULL
304774	Vessel 9	Dry	Bulk Carrier	Other	Owner 9	Company 2	11/2/2017	NULL
304773	Vessel 10	Dry	Bulk Carrier	MAN B&W	Owner 10	Company 2	11/3/2017	NULL
304772	Vessel 11	Dry	Container	< MAN B&W >	Owner 11	Company 2	11/4/2017	NULL
304771	Vessel 12	Dry	Container	< MAN B&W >	Owner 12	Company 2	11/5/2017	NULL
304770	Vessel 13	Dry	Container	Burmeister & Wain	Owner 13	Company 2	11/6/2017	NULL
304769	Vessel 14	Dry	Container	-MITSUBISHI-	Owner 14	Company 2	9/29/2017	NULL
304768	Vessel 15	Dry	Container	< MAN B&W >	Owner 15	Company 2	9/29/2017	NULL
304767	Vessel 16	Dry	Container	< SULZER >	Owner 1	Company 2	9/29/2017	NULL
304766	Vessel 17	Dry	Container	< SULZER >	Owner 2	Company 2	9/29/2017	NULL
304765	Vessel 18	Dry	Container	< MAN B&W >	Owner 3	Company 2	9/29/2017	NULL
304764	Vessel 19	Dry	Container	< MAN B&W >	Owner 4	Company 2	9/29/2017	NULL
304763	Vessel 20	Dry	Container	Burmeister & Wain	Owner 5	Company 2	9/29/2017	NULL
304762	Vessel 21	Dry	Container	< MAN B&W >	Owner 6	Company 2	9/29/2017	NULL
304761	Vessel 22	Dry	Container	< MAN B&W >	Owner 7	Company 2	9/29/2017	NULL
304760	Vessel 23	Dry	Bulk Carrier	Burmeister & Wain	Owner 8	Company 2	9/29/2017	NULL
304759	Vessel 24	Tanker	Oil Tanker	Burmeister & Wain	Owner 9	Company 4	10/26/2017	NULL
304758	Vessel 25	Offshore	Offshore	Other	Owner 10	Company 2	10/27/2017	NULL
304757	Vessel 26	Dry	Bulk Carrier	MAN B&W	Owner 11	Company 5	10/28/2017	NULL
304756	Vessel 27	Offshore	Offshore	Other	Owner 12	Company 2	10/22/2017	NULL
304755	Vessel 28	Tanker	Oil Tanker	< MAN B&W >	Owner 13	Company 1	10/23/2017	NULL
304754	Vessel 29	Tanker	Oil Tanker	STX	Owner 14	Company 6	10/24/2017	NULL
304753	Vessel 30	Gas	Gas Tanker	-WARTSILA-	Owner 15	Company 3	10/25/2017	NULL

The first requirement was to get a list of active vessels between a user defined date range using two columns from this table i.e. 'Primary Manager Valid from' & 'Primary Manager Valid to'.

@v-jiascu-msft help me with the DAX required for the result which was as follows:

Vessel Count = CALCULATE(DISTINCTCOUNT('Vessel Register'[ID]), filter('Vessel Register', 'Vessel Register'[PM Start Date]<=min('Calendar'[Date])&&'Vessel Register'[PM End Date]>=max('Calendar'[Date])))

where i used the following query to build the calendar table "Calendar = CALENDARAUTO()" also recommended by @v-jiascu-msft

Now i have a second table, 

Job ID	DONE DATE	Vessel ID	PLANNED JOB ID	UNPLANNED JOB ID	DUE DATE	JOB TITLE
1001000090	7/7/2016	304782	99990109793	99990110451	9/12/2016	RENEW WIRE ROPES
1001000092	7/7/2016	304781	99990109795	99990110453	11/20/2016	RENEW WIRE ROPES
1001000093	7/8/2016	304780	99990109800	99990110458	9/12/2016	RENEW WIRE ROPES
1001000094	7/7/2016	304758	99990109796	99990110454	11/20/2016	RENEW WIRE ROPES
1001000339	7/31/2016	304757	99990109232	99990109890	8/3/2016	I523 - INSPECT PURIFIER BRAKE
1001000841	1/28/2017	304756	99990109319	99990109977	1/28/2017	FULL OVERHAUL ELECTRIC MOTOR.
1001000842	4/30/2016	304755	99990109315	99990109973	8/13/2016	FULL OVERHAUL ELECTRIC MOTOR.
1001000843	4/30/2016	304754	99990109274	99990109932	12/30/2016	FULL OVERHAUL ELECTRIC MOTOR.
1001000844	8/31/2017	304753	99990109092	99990109750	8/12/2017	FULL OVERHAUL ELECTRIC MOTOR.
1001000845	12/8/2016	304782	99990109390	99990110048	12/30/2016	FULL OVERHAUL ELECTRIC MOTOR.
1001000846	12/5/2016	304781	99990109392	99990110050	12/30/2016	FULL OVERHAUL ELECTRIC MOTOR.
1001000849	12/2/2016	304780		99990110044	12/30/2016	FULL OVERHAUL ELECTRIC MOTOR.
1001000850	5/31/2016	304758		99990109924	7/28/2016	FULL OVERHAUL ELECTRIC MOTOR.
1001000852	12/27/2015	304757		99990110042	4/27/2016	FULL OVERHAUL ELECTRIC MOTOR.
1001000853	2/29/2016	304756	99990109323	99990109981	2/27/2016	FULL OVERHAUL ELECTRIC MOTOR.
1001000986	4/30/2016	304755	99990109307	99990109965	6/26/2016	I530 - INSPECT-PURIFIER + BALL BEARINGS
1001001017	1/1/2016	304754	99990109517	99990110175	1/1/2016	GMDSS SHORE-BASED MAINTENANCE
1001001267	12/1/2015	304753	99990109803	99990110461	1/10/2016	CHANGE OIL IN TURNING DEVICE
1001001334	6/30/2016	304782	99990109104	99990109762	10/23/2016	OVERHAUL PUMP
1001001335	10/19/2016	304781	99990109385	99990110043	12/17/2016	OVERHAUL PUMP
1001001336	4/16/2017	304780		99990109929	11/5/2017	OVERHAUL PUMP
1001001370	10/18/2016	304758		99990110047	12/16/2016	OVERHAUL PUMP
1001001448	1/7/2016	304757		99990110513	1/28/2016	TANK CLEANING.
1001001632	2/5/2016	304756		99990112049	2/5/2016	OIL MIST ANNUAL MAINTENANCE
1001001833	1/1/2016	304755		99990110161	1/1/2016	FLAG STATE ANNUAL TAXES
1001002152	2/26/2017	304754	99990109387	99990110045	2/26/2017	OVERHAUL PUMP
1001002202	1/18/2016	304753	99990109693	99990110351	1/18/2016	ANNUAL MACHINERY SURVEY
1001002418	2/5/2016	304782	99990110086	99990110744	2/28/2016	BLOWER CONDITION CHECK - ANNUALLY
1001002471	7/27/2015	304781	99990109477	99990110135	7/5/2016	L019 - CHANGE OIL & CLEAN / REPLACE FILTER

Is it possible to prepare a dax which will give me a distinct count of all unplanned job ID based on the same max and minimum date filter I applied on the dax above. The date filter needs to be applied on the "done date' column. So when I select the vessel count dax, it should immidiately show me disctinct count of unplanned job ids. 

Any help on this matter will be greatly appreaciated.

Best regards,

Bikram

[v-chuncz-msft]

@bikram_laishram,

It seems that you may just replace the column name in formula above. To get a more accurate answer, simplify the example and show us the expected output.