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

     

     

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

      • bikram_laishram's avatar
        bikram_laishram
        Helper I
        8 years ago

        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