Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bikram_laishram
Helper I
Helper I

Custom Count from a date range

‎12-21-2017 10:42 PM

 

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
Labels:
Message 1 of 8
1,711 Views
0
1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee
In response to bikram_laishram

‎12-26-2017 06:15 PM

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

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 5 of 8
1,835 Views
7 REPLIES 7
v-jiascu-msft
Employee
Employee

‎12-24-2017 10:08 PM

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

Custom Count from a date range.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 3 of 8
1,688 Views
bikram_laishram
Helper I
Helper I
In response to v-jiascu-msft

‎12-26-2017 02:07 AM

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

 

Message 4 of 8
1,681 Views
0
v-jiascu-msft
Employee
Employee
In response to bikram_laishram

‎12-26-2017 06:15 PM

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

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 5 of 8
1,836 Views
bikram_laishram
Helper I
Helper I
In response to v-jiascu-msft

‎12-27-2017 12:47 AM

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

Message 6 of 8
1,667 Views
0
v-jiascu-msft
Employee
Employee
In response to bikram_laishram

‎12-27-2017 02:31 AM

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 7 of 8
1,661 Views
bikram_laishram
Helper I
Helper I
In response to v-jiascu-msft

‎12-27-2017 04:33 AM

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

Message 8 of 8
1,657 Views
0
mehaboob557
Resolver IV
Resolver IV

‎12-23-2017 11:32 AM

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

Message 2 of 8
1,700 Views

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All