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
