## Desktop

Frequent Visitor
Posts: 12
Registered: ‎09-28-2016

# Need to find date range using timeline slicer filter

[ Edited ]

Hey guys, I have an urgent requirement to resolve..! Its a quite long post so I thank you for your patience.

For readablity purpose, have splitted my question into two parts: Scenario and Problem Statement:

Scenario:

My task is to find the time utilized by certain products leased at 'no of day' basis. For this I have used Timeline Slicer as shown below:

Timeline Slicer Input

I have a dataset as follows, from which I need to provide the time utilization for the selected period.

 Eq.Delivery Date Eq.Collection Date 05-Jan-16 06-Jan-16 08-Jan-16 08-Jan-16 10-Jan-16 11-Jan-16 11-Jan-16 13-Feb-16

For example, I have selected a time range: 01-Jan-2016 to 15-Jan-2016 in my Timeline slicer. In that period of time, my products were actively leased on following dates (data derived from the above table):

 Actual Active Dates 05-Jan-16 06-Jan-16 08-Jan-16 10-Jan-16 11-Jan-16 12-Jan-16 13-Jan-16 14-Jan-16 15-Jan-16

and the resultant period is:

 Eq.Delivery Date Eq.Collection Date No of days active Comments 05-Jan-16 06-Jan-16 2 08-Jan-16 08-Jan-16 1 10-Jan-16 11-Jan-16 2 12-Jan-16 13-Feb-16 4 Since our maximum selection of date in slicer is 15-Jan-2016, diff b/w 12-jan and 15-jan should be taken

Problem Statement:

1. I need a way to extract the [StartDate] and [EndDate] from this slicer. (Here StartDate = 01-Jan-16 and [EndDate] = 15-Jan-16)

2. I also need to find the No. of Days (as shown in the above table), to calculate the time utilization. My utilzation % will be

[no. of days]/[StartDate - EndDate]. (Here it will be 9/15 = 60%).

Frequent Visitor
Posts: 28
Registered: ‎09-26-2016

## Re: Need to find date range using timeline slicer filter

First make calculation of the minimum and maximum date based on the same column you use for the time slicer:

MinDate := CALCULATE ( MIN ('Table'[Eq.CreatedDate]) ; ALLEXCEPT ( 'Table'[Eq.CreatedDate] ) )

MaxDate :=CALCULATE ( MAX ('Table'[Eq.CreatedDate]) ; ALLEXCEPT ( 'Table'[Eq.CreatedDate] ) )

Utilization % := DIVIDE ( [no. of days] ; [MaxDate] - [MinDate] )

However, if you want to display the utilization in a table with columns you might need some changes to the formulas, but I believe this will work for a CARD / KPI value .

Frequent Visitor
Posts: 12
Registered: ‎09-28-2016

## Re: Need to find date range using timeline slicer filter

[ Edited ]

As you can see, time slicer shows selection from 02-Jan-2016 to 10-Jan-2016. However I wanted to select range to start from 01-Jan-2016. The min and max dates shows those date values which has data in my table (i.e., 9-jan has date mentioned in my data and is shown here, 10-jan is not available in my table and hence not shown by maxDate).

However I want 01-Jan-2016 and 10-Jan-2016 also to be included in my CARD. A snapshot of my data is given below:

 Actual Data Expected Output Eq.Delivery Date Eq.Collection Date Eq.CreatedDate MinDate MaxDate Diff of col A and B Utilization (%) 03-Jan-16 04-Jan-16 02-Jan-16 01-Jan-16 10-Jan-16 2 20 04-Jan-16 05-Jan-16 04-Jan-16 01-Jan-16 10-Jan-16 2 20 05-Jan-16 05-Jan-16 05-Jan-16 01-Jan-16 10-Jan-16 1 10 07-Jan-16 08-Jan-16 07-Jan-16 01-Jan-16 10-Jan-16 2 20 08-Jan-16 08-Jan-16 08-Jan-16 01-Jan-16 10-Jan-16 1 10 09-Jan-16 10-Jan-16 09-Jan-16 01-Jan-16 10-Jan-16 2 20

PS: Creation Date is used for the time slicer. Difference between Delivery Date and Collection Date is needed.

Frequent Visitor
Posts: 28
Registered: ‎09-26-2016

## Re: Need to find date range using timeline slicer filter

I would suggest to create a date table with continous dates and make a many-to-one relationship between your table and the new date table. (Maybe use created date as relationship key?)

MinDate = MIN ('DateTable'[Date])

MinDate = MAX ('DateTable'[Date])

Change the MinDate and MaxDate measures to use the date column in the new date table instead. Also use the same column for the timeslicer. This will most likely fix it.

As for the calculation of days between delivery date and collection date you can add a calculated column that measures the difference between each row. Create a SUM measure to capture this value.

CalculatedColumn = 'Table'[Collection Date]-'Table'[Delivery Date]

Measure = SUM ('Table'[CalculatedColumn])

Community Support Team
Posts: 5,652
Registered: ‎09-21-2016

## Re: Need to find date range using timeline slicer filter

Hi, @Bhoobala_P

Instead of using Creation Date to create time slicer, you should use the Date value in a calendar table. Then create a one to many relationship between calendar table and the actual data table (in my test, it’s Table1), please note that you have to set the cross filter direction to Single.

Sample data in my test:

Design Surface:

DAX formula for measures:

`StartDate = IF(SUM(Table1[SALES])=BLANK(),BLANK(),CALCULATE(MIN('calendar'[DATE]),ALLSELECTED('calendar'[DATE])))EndDate = IF(SUM(Table1[SALES])=BLANK(),BLANK(),CALCULATE(MAX('calendar'[DATE]),ALLSELECTED('calendar'[DATE])))DateRange = IF(SUM(Table1[SALES])=BLANK(),BLANK(),DATEDIFF(Table1[StartDate2],Table1[EndDate2],DAY))Diff = IF(SUM(Table1[SALES])=BLANK(),BLANK(),DATEDIFF(MAX(Table1[DeliveryDate]),MAX(Table1[CollectionDate]),DAY))Utilization = IF(SUM(Table1[SALES])=BLANK(),BLANK(),DIVIDE(Table1[Diff],Table1[DateRange]))`

By the way, in each measure’s expression, remember to use IF(SUM(Table1[SALES])=BLANK(),BLANK() , ) to check the value of all columns except for DeliveryDate and CollectionDate in the actual data table.

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 12
Registered: ‎09-28-2016

## Re: Need to find date range using timeline slicer filter

Dear Kris,

I mapped as you said and that infact is a useful solution. I can get correct start date and end date from timeslicer if I use Hierarchy (custom visual). However, if I use Timeline Slicer (again a custom visual), I am getting static dates for Start Date and End Date. So there is a last piece of bug in the viz. My code is as follows:

newStartDate = CALCULATE ( MIN ( myNewTable[newDateColumn].[Date] ), ALLSELECTED ( myNewTable[newDateColumn].[Date] ) )

newEndDate = CALCULATE ( MAX ( myNewTable[newDateColumn].[Date] ), ALLSELECTED ( myNewTable[newDateColumn].[Date] ) )

Here is a snap shot of both the scenarios:

Case 1, the Start Date and End Date should've been 02-01-2016 and 06-01-2016, but it shows 01-01-2016 and 31-12-2017 instead.

Case 2, things are intact.

The problem is that I need to use the Timeline Slicer for my viz. Am confused why it works on one viz and not on another..!!

Timeline Slicer

Hierarchy Slicer

Highlighted
Frequent Visitor
Posts: 28
Registered: ‎09-26-2016

## Re: Need to find date range using timeline slicer filter

Very strange. I'm not sure but maybe if you remove the ALLSELECTED filter from your calculations..? Custom visuals can't always be trusted.

Kristoffer