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 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_IdVessel_Object_IdVessel_NameVessel_FleetVessel_TypeMaker_NameModel_NameFlag_NameUltimate_OwnerPrimary_ManagerSub_Manager_IdSub_ManagerPrimary_Manager_Valid_FromPrimary_Manager_Valid_To
304782121030Vessel 1DryContainer< MAN B&W >9K90MC-CMarshall IslandsOwner 1Company 1-1N/A9/29/20154/29/2017
304781864659Vessel 2TankerOil TankerBurmeister & Wain7S80MCMarshall IslandsOwner 2Company 2-1N/A4/30/20154/30/2017
3047803607Vessel 3GasGas TankerBurmeister & Wain5S35MCIsle Of ManOwner 3Company 3-1N/ANULL5/1/2017
304779865026Vessel 4DryBulk Carrier< MAN B&W >5S60ME-C8.1PortugalOwner 4Company 2-1N/A9/29/20175/2/2017
304778865025Vessel 5DryContainerBurmeister & Wain7L 60 MCLiberiaOwner 5Company 2-1N/A9/29/20177/3/2017
304777865024Vessel 6DryBulk Carrier< MAN B&W >6G70ME C9.2 TIIPortugalOwner 6Company 2-1N/A9/29/20177/4/2017
304776865023Vessel 7DryBulk CarrierMAN B&W5S50MC-C8Marshall IslandsOwner 7Company 6-1N/A10/31/20178/5/2017
3047756743Vessel 8DryContainer< SULZER >8RTA96CLiberiaOwner 8Company 2-1N/A10/26/20168/6/2017
304774864753Vessel 9DryBulk CarrierOtherOTHERMarshall IslandsOwner 9Company 2-1N/A10/27/20168/7/2017
304773864724Vessel 10DryBulk CarrierMAN B&W6S60ME-C8Marshall IslandsOwner 10Company 2-1N/A10/28/201610/8/2017
30477240417Vessel 11DryContainer< MAN B&W >12K98MCPortugalOwner 11Company 2-1N/A10/29/201610/9/2017
3047716919Vessel 12DryContainer< MAN B&W >12K90MCLiberiaOwner 12Company 2-1N/A10/30/201610/10/2017
3047706955Vessel 13DryContainerBurmeister & Wain7S70MCLiberiaOwner 13Company 2-1N/A10/31/201610/11/2017
304769865022Vessel 14DryContainer-MITSUBISHI-8 UEC 60 LSLiberiaOwner 14Company 2-1N/A11/1/201610/12/2017
304768865021Vessel 15DryContainer< MAN B&W >6G60ME-C9Marshall IslandsOwner 15Company 2-1N/A11/2/201610/13/2017
304767865020Vessel 16DryContainer< SULZER >7RTA84CLiberiaOwner 1Company 2-1N/A11/3/201612/14/2017
3047666782Vessel 17DryContainer< SULZER >7RTA72U-BLiberiaOwner 2Company 2-1N/A11/4/201612/15/2017
304765865019Vessel 18DryContainer< MAN B&W >6G60ME-C9Marshall IslandsOwner 3Company 2-1N/A11/5/201612/16/2017
304764865018Vessel 19DryContainer< MAN B&W >6G60ME-C9Marshall IslandsOwner 4Company 2-1N/A11/6/201612/17/2017
304763865017Vessel 20DryContainerBurmeister & Wain5S50MC-CLiberiaOwner 5Company 2-1N/A11/7/201612/18/2017
304762865016Vessel 21DryContainer< MAN B&W >7L58/64LiberiaOwner 6Company 2-1N/A11/8/201612/19/2017
304761865015Vessel 22DryContainer< MAN B&W >5S50MC-CLiberiaOwner 7Company 2-1N/A11/9/201612/20/2017
304760865014Vessel 23DryBulk CarrierBurmeister & Wain7S70 MC-CPanamaOwner 8Company 2-1N/A11/10/201612/21/2017
304759865010Vessel 24TankerOil TankerBurmeister & Wain6S60MCIndiaOwner 9Company 4-1N/A11/11/201612/22/2017
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

     

     

7 Replies

  • mehaboob557's avatar
    mehaboob557
    Resolver III
    8 years ago

    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

  • 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