Forum Discussion

bikram_laishram's avatar
bikram_laishram
Helper I
8 years ago
Solved

Custom Count from a date range

  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 ves...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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).

    Custom_Count_from_a_date_range2

     

     

    Best Regards,

    Dale

     

     

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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

bikram_laishram's avatar
bikram_laishram
Helper I
8 years ago

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

 

IDVessel NameVessel TypePrimary ManagerPrimary Manager Valid from Primary Manager Valid to
246000001 Vessel 1Type 1Company 11-Jan-151-Jan-16
246000002 Vessel 2Type 2Company 21-Feb-151-Jan-16
246000003 Vessel 3Type 3Company 31-Mar-151-Jan-16
246000004 Vessel 4Type 1Company 41-Apr-151-Jan-16
246000005 Vessel 5Type 2Company 11-May-155-Feb-17
246000006 Vessel 6Type 3Company 21-Jun-155-Feb-17
246000007 Vessel 7Type 1Company 31-Jul-155-Feb-17
246000008 Vessel 8Type 2Company 41-Aug-155-Feb-17
246000009 Vessel 9Type 3Company 11-Sep-155-Feb-17
246000010 Vessel 10Type 1Company 21-Oct-155-Feb-17
246000011 Vessel 11Type 2Company 31-Nov-156-Mar-17
246000012 Vessel 12Type 3Company 41-Dec-156-Mar-17
246000013 Vessel 13Type 1Company 11-Jan-166-Mar-17
246000014 Vessel 14Type 2Company 21-Feb-166-Mar-17
246000015 Vessel 15Type 3Company 31-Mar-166-Mar-17
246000016 Vessel 16Type 1Company 41-Apr-1616-Sep-17
246000017 Vessel 17Type 2Company 11-May-1616-Sep-17
246000018 Vessel 18Type 3Company 21-Jun-1616-Sep-17
246000019 Vessel 19Type 1Company 31-Jul-1616-Sep-17
246000020 Vessel 20Type 2Company 41-Aug-1616-Sep-17
246000021 Vessel 21Type 3Company 11-Sep-1621-Jun-17
246000022 Vessel 22Type 1Company 21-Oct-1621-Jun-17
246000023 Vessel 23Type 2Company 31-Nov-1621-Jun-17
246000024 Vessel 24Type 3Company 41-Dec-1621-Jun-17
246000025 Vessel 25Type 1Company 11-Jan-171-Nov-17
246000026 Vessel 26Type 2Company 21-Feb-171-Nov-17
246000027 Vessel 27Type 3Company 31-Mar-171-Nov-17
246000028 Vessel 28Type 1Company 41-Apr-171-Nov-17
246000029 Vessel 29Type 2Company 11-May-171-Nov-17
246000030 Vessel 30Type 3Company 21-Jun-171-Nov-17

 

Vessel Distribution as Company (for May 2017) {Active vessels are all vessels that are valid in May 2017) 

 

Vessel NameVessel TypePrimary ManagerPrimary Manager Valid from Primary Manager Valid to
 Vessel 16Type 1Company 41-Apr-1616-Sep-17
 Vessel 17Type 2Company 11-May-1616-Sep-17
 Vessel 18Type 3Company 21-Jun-1616-Sep-17
 Vessel 19Type 1Company 31-Jul-1616-Sep-17
 Vessel 20Type 2Company 41-Aug-1616-Sep-17
 Vessel 21Type 3Company 11-Sep-1621-Jun-17
 Vessel 22Type 1Company 21-Oct-1621-Jun-17
 Vessel 23Type 2Company 31-Nov-1621-Jun-17
 Vessel 24Type 3Company 41-Dec-1621-Jun-17
 Vessel 25Type 1Company 11-Jan-171-Nov-17
 Vessel 26Type 2Company 21-Feb-171-Nov-17
 Vessel 27Type 3Company 31-Mar-171-Nov-17
 Vessel 28Type 1Company 41-Apr-171-Nov-17
 Vessel 29Type 2Company 11-May-171-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 ManagerCount
Company 14
Company 23
Company 33
Company 44

 

Count of active vessels as per Vessel Type for May 2017 will be as follows

Vessel TypeCount
Type 15
Type 25
Type 34

 

This will realy help in a lot of calculations for us. 

 

Thanks in Advance

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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).

    Custom_Count_from_a_date_range2

     

     

    Best Regards,

    Dale

     

     

    • bikram_laishram's avatar
      bikram_laishram
      Helper I
      8 years ago

      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

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        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