Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Folks,
Merry X'mas & Happy New year.
I have a table where we have two date columns i.e. "from Date" and a "to date" column. How do I create a DAX measure or query to get count of vessel(vessel_name) for a month that is falling in that range.
For example: Vessel 1 will be part of all results where the selected date range falls between, 9/29/2015 & 4/29/2017. If a user selects for example, may '17, vessel 1 will not be part of that count. Please assist as this is causing we need to make lot of analysis based on information from a date range.
Vessel_Id | Vessel_Object_Id | Vessel_Name | Vessel_Fleet | Vessel_Type | Maker_Name | Model_Name | Flag_Name | Ultimate_Owner | Primary_Manager | Sub_Manager_Id | Sub_Manager | Primary_Manager_Valid_From | Primary_Manager_Valid_To |
304782 | 121030 | Vessel 1 | Dry | Container | < MAN B&W > | 9K90MC-C | Marshall Islands | Owner 1 | Company 1 | -1 | N/A | 9/29/2015 | 4/29/2017 |
304781 | 864659 | Vessel 2 | Tanker | Oil Tanker | Burmeister & Wain | 7S80MC | Marshall Islands | Owner 2 | Company 2 | -1 | N/A | 4/30/2015 | 4/30/2017 |
304780 | 3607 | Vessel 3 | Gas | Gas Tanker | Burmeister & Wain | 5S35MC | Isle Of Man | Owner 3 | Company 3 | -1 | N/A | NULL | 5/1/2017 |
304779 | 865026 | Vessel 4 | Dry | Bulk Carrier | < MAN B&W > | 5S60ME-C8.1 | Portugal | Owner 4 | Company 2 | -1 | N/A | 9/29/2017 | 5/2/2017 |
304778 | 865025 | Vessel 5 | Dry | Container | Burmeister & Wain | 7L 60 MC | Liberia | Owner 5 | Company 2 | -1 | N/A | 9/29/2017 | 7/3/2017 |
304777 | 865024 | Vessel 6 | Dry | Bulk Carrier | < MAN B&W > | 6G70ME C9.2 TII | Portugal | Owner 6 | Company 2 | -1 | N/A | 9/29/2017 | 7/4/2017 |
304776 | 865023 | Vessel 7 | Dry | Bulk Carrier | MAN B&W | 5S50MC-C8 | Marshall Islands | Owner 7 | Company 6 | -1 | N/A | 10/31/2017 | 8/5/2017 |
304775 | 6743 | Vessel 8 | Dry | Container | < SULZER > | 8RTA96C | Liberia | Owner 8 | Company 2 | -1 | N/A | 10/26/2016 | 8/6/2017 |
304774 | 864753 | Vessel 9 | Dry | Bulk Carrier | Other | OTHER | Marshall Islands | Owner 9 | Company 2 | -1 | N/A | 10/27/2016 | 8/7/2017 |
304773 | 864724 | Vessel 10 | Dry | Bulk Carrier | MAN B&W | 6S60ME-C8 | Marshall Islands | Owner 10 | Company 2 | -1 | N/A | 10/28/2016 | 10/8/2017 |
304772 | 40417 | Vessel 11 | Dry | Container | < MAN B&W > | 12K98MC | Portugal | Owner 11 | Company 2 | -1 | N/A | 10/29/2016 | 10/9/2017 |
304771 | 6919 | Vessel 12 | Dry | Container | < MAN B&W > | 12K90MC | Liberia | Owner 12 | Company 2 | -1 | N/A | 10/30/2016 | 10/10/2017 |
304770 | 6955 | Vessel 13 | Dry | Container | Burmeister & Wain | 7S70MC | Liberia | Owner 13 | Company 2 | -1 | N/A | 10/31/2016 | 10/11/2017 |
304769 | 865022 | Vessel 14 | Dry | Container | -MITSUBISHI- | 8 UEC 60 LS | Liberia | Owner 14 | Company 2 | -1 | N/A | 11/1/2016 | 10/12/2017 |
304768 | 865021 | Vessel 15 | Dry | Container | < MAN B&W > | 6G60ME-C9 | Marshall Islands | Owner 15 | Company 2 | -1 | N/A | 11/2/2016 | 10/13/2017 |
304767 | 865020 | Vessel 16 | Dry | Container | < SULZER > | 7RTA84C | Liberia | Owner 1 | Company 2 | -1 | N/A | 11/3/2016 | 12/14/2017 |
304766 | 6782 | Vessel 17 | Dry | Container | < SULZER > | 7RTA72U-B | Liberia | Owner 2 | Company 2 | -1 | N/A | 11/4/2016 | 12/15/2017 |
304765 | 865019 | Vessel 18 | Dry | Container | < MAN B&W > | 6G60ME-C9 | Marshall Islands | Owner 3 | Company 2 | -1 | N/A | 11/5/2016 | 12/16/2017 |
304764 | 865018 | Vessel 19 | Dry | Container | < MAN B&W > | 6G60ME-C9 | Marshall Islands | Owner 4 | Company 2 | -1 | N/A | 11/6/2016 | 12/17/2017 |
304763 | 865017 | Vessel 20 | Dry | Container | Burmeister & Wain | 5S50MC-C | Liberia | Owner 5 | Company 2 | -1 | N/A | 11/7/2016 | 12/18/2017 |
304762 | 865016 | Vessel 21 | Dry | Container | < MAN B&W > | 7L58/64 | Liberia | Owner 6 | Company 2 | -1 | N/A | 11/8/2016 | 12/19/2017 |
304761 | 865015 | Vessel 22 | Dry | Container | < MAN B&W > | 5S50MC-C | Liberia | Owner 7 | Company 2 | -1 | N/A | 11/9/2016 | 12/20/2017 |
304760 | 865014 | Vessel 23 | Dry | Bulk Carrier | Burmeister & Wain | 7S70 MC-C | Panama | Owner 8 | Company 2 | -1 | N/A | 11/10/2016 | 12/21/2017 |
304759 | 865010 | Vessel 24 | Tanker | Oil Tanker | Burmeister & Wain | 6S60MC | India | Owner 9 | Company 4 | -1 | N/A | 11/11/2016 | 12/22/2017 |
Solved! Go to Solution.
Hi @bikram_laishram,
Did you try out my solution? It should work. You need a time line slicer from the Store. Please check the file here. (don't create relationship between two tables).
Best Regards,
Dale
Hi @bikram_laishram,
The final result isn't clear. Maybe you can try it like this. You can check it out in this file in which I create two possible result.
1. A date table without relationship to other table.
Calendar = CALENDARAUTO()
2. Create a measure.
Result = CALCULATE ( COUNT ( 'Table1'[Vessel_Name] ), FILTER ( 'Table1', 'Table1'[Primary_Manager_Valid_From] <= MIN ( 'Calendar'[Date] ) && 'Table1'[Primary_Manager_Valid_To] >= MAX ( 'Calendar'[Date] ) ) )
Best Regards
Dale
Hi,
Merry Xmas Buddy. Thank you for the prompt response. The measure is pretty good but not useful in my scenario, where I need for the number of active vessels for each primary manager or vessel type. For example
ID | Vessel Name | Vessel Type | Primary Manager | Primary Manager Valid from | Primary Manager Valid to |
246000001 | Vessel 1 | Type 1 | Company 1 | 1-Jan-15 | 1-Jan-16 |
246000002 | Vessel 2 | Type 2 | Company 2 | 1-Feb-15 | 1-Jan-16 |
246000003 | Vessel 3 | Type 3 | Company 3 | 1-Mar-15 | 1-Jan-16 |
246000004 | Vessel 4 | Type 1 | Company 4 | 1-Apr-15 | 1-Jan-16 |
246000005 | Vessel 5 | Type 2 | Company 1 | 1-May-15 | 5-Feb-17 |
246000006 | Vessel 6 | Type 3 | Company 2 | 1-Jun-15 | 5-Feb-17 |
246000007 | Vessel 7 | Type 1 | Company 3 | 1-Jul-15 | 5-Feb-17 |
246000008 | Vessel 8 | Type 2 | Company 4 | 1-Aug-15 | 5-Feb-17 |
246000009 | Vessel 9 | Type 3 | Company 1 | 1-Sep-15 | 5-Feb-17 |
246000010 | Vessel 10 | Type 1 | Company 2 | 1-Oct-15 | 5-Feb-17 |
246000011 | Vessel 11 | Type 2 | Company 3 | 1-Nov-15 | 6-Mar-17 |
246000012 | Vessel 12 | Type 3 | Company 4 | 1-Dec-15 | 6-Mar-17 |
246000013 | Vessel 13 | Type 1 | Company 1 | 1-Jan-16 | 6-Mar-17 |
246000014 | Vessel 14 | Type 2 | Company 2 | 1-Feb-16 | 6-Mar-17 |
246000015 | Vessel 15 | Type 3 | Company 3 | 1-Mar-16 | 6-Mar-17 |
246000016 | Vessel 16 | Type 1 | Company 4 | 1-Apr-16 | 16-Sep-17 |
246000017 | Vessel 17 | Type 2 | Company 1 | 1-May-16 | 16-Sep-17 |
246000018 | Vessel 18 | Type 3 | Company 2 | 1-Jun-16 | 16-Sep-17 |
246000019 | Vessel 19 | Type 1 | Company 3 | 1-Jul-16 | 16-Sep-17 |
246000020 | Vessel 20 | Type 2 | Company 4 | 1-Aug-16 | 16-Sep-17 |
246000021 | Vessel 21 | Type 3 | Company 1 | 1-Sep-16 | 21-Jun-17 |
246000022 | Vessel 22 | Type 1 | Company 2 | 1-Oct-16 | 21-Jun-17 |
246000023 | Vessel 23 | Type 2 | Company 3 | 1-Nov-16 | 21-Jun-17 |
246000024 | Vessel 24 | Type 3 | Company 4 | 1-Dec-16 | 21-Jun-17 |
246000025 | Vessel 25 | Type 1 | Company 1 | 1-Jan-17 | 1-Nov-17 |
246000026 | Vessel 26 | Type 2 | Company 2 | 1-Feb-17 | 1-Nov-17 |
246000027 | Vessel 27 | Type 3 | Company 3 | 1-Mar-17 | 1-Nov-17 |
246000028 | Vessel 28 | Type 1 | Company 4 | 1-Apr-17 | 1-Nov-17 |
246000029 | Vessel 29 | Type 2 | Company 1 | 1-May-17 | 1-Nov-17 |
246000030 | Vessel 30 | Type 3 | Company 2 | 1-Jun-17 | 1-Nov-17 |
Vessel Distribution as Company (for May 2017) {Active vessels are all vessels that are valid in May 2017)
Vessel Name | Vessel Type | Primary Manager | Primary Manager Valid from | Primary Manager Valid to |
Vessel 16 | Type 1 | Company 4 | 1-Apr-16 | 16-Sep-17 |
Vessel 17 | Type 2 | Company 1 | 1-May-16 | 16-Sep-17 |
Vessel 18 | Type 3 | Company 2 | 1-Jun-16 | 16-Sep-17 |
Vessel 19 | Type 1 | Company 3 | 1-Jul-16 | 16-Sep-17 |
Vessel 20 | Type 2 | Company 4 | 1-Aug-16 | 16-Sep-17 |
Vessel 21 | Type 3 | Company 1 | 1-Sep-16 | 21-Jun-17 |
Vessel 22 | Type 1 | Company 2 | 1-Oct-16 | 21-Jun-17 |
Vessel 23 | Type 2 | Company 3 | 1-Nov-16 | 21-Jun-17 |
Vessel 24 | Type 3 | Company 4 | 1-Dec-16 | 21-Jun-17 |
Vessel 25 | Type 1 | Company 1 | 1-Jan-17 | 1-Nov-17 |
Vessel 26 | Type 2 | Company 2 | 1-Feb-17 | 1-Nov-17 |
Vessel 27 | Type 3 | Company 3 | 1-Mar-17 | 1-Nov-17 |
Vessel 28 | Type 1 | Company 4 | 1-Apr-17 | 1-Nov-17 |
Vessel 29 | Type 2 | Company 1 | 1-May-17 | 1-Nov-17 |
All the above vessels are available in May 2017. Vessel 1 - 15 are valid upto 6th March 2017, hence cannot be part of the count. Vessel 30 is valide from 1- Jun 2017 and hence can not be part of the count. So the count will be as follows for the month of May 2017
Primary Manager | Count |
Company 1 | 4 |
Company 2 | 3 |
Company 3 | 3 |
Company 4 | 4 |
Count of active vessels as per Vessel Type for May 2017 will be as follows
Vessel Type | Count |
Type 1 | 5 |
Type 2 | 5 |
Type 3 | 4 |
This will realy help in a lot of calculations for us.
Thanks in Advance
Hi @bikram_laishram,
Did you try out my solution? It should work. You need a time line slicer from the Store. Please check the file here. (don't create relationship between two tables).
Best Regards,
Dale
Hi Dale,
At the cost of soudning like an village idiot, I am sorry I had added a relationship between the two tables and hence was not able to achieve the desired result. As soon as I deleted the relationship it worked like a breeze.
Hope you didn't have to spend too much time on this.
Thank you once again.
Merry XMas once again and have a great year ahead
Regards
Bikram
Hi Bikram,
My pleasure. It's because the relationship will filter down the records that we don't create a relationship. I'm glad you can get it work. Merry Christmas and Happy New Year.
Best Regards,
Dale
Hi Dale,
@v-jiascu-msft
I have one more table(Job order) in the same report which has a date column and three other columns, which contain ID (foreign keys). I need to have a distinct count of these ids to calculate a frequency based on the same slicer selection. Now the vessel table and job order tables are linked to get a count of breakdown jobs and planed jobs. Since I can not have any relationship between the vessel table and calendar, do you think it will be possible to still get a unique count using the calendar table
Best regards,
Bikram
Hi @bikram_laishram,
Keep the date in the filter namely "slicer". So, if you select date range the values will automatically show under that date range only.
Use the below measure to calculate the count,
Measure Count = CALCULATE(COUNT(vessel(vessel_name)), ALLEXCEPT(vessel,vessel(vessel_name)))
Hope this works for you 🙂
Regards,
Mehbub
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
34 | |
30 | |
18 | |
12 | |
8 |
User | Count |
---|---|
50 | |
35 | |
30 | |
15 | |
12 |