Forum Discussion
Custom Count from a date range
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,
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