Reply
Highlighted
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.jpgTimeline Slicer Input

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

 

Eq.Delivery DateEq.Collection Date
05-Jan-1606-Jan-16
08-Jan-1608-Jan-16
10-Jan-1611-Jan-16
11-Jan-1613-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 DateEq.Collection DateNo of days activeComments
05-Jan-1606-Jan-162 
08-Jan-1608-Jan-161 
10-Jan-1611-Jan-162 
12-Jan-1613-Feb-16 4Since 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 ]

kris, your solution had solved half of it. however issues persists..! 

test.jpg

 

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 DateEq.Collection DateEq.CreatedDate MinDateMaxDateDiff of col A and BUtilization (%)
03-Jan-1604-Jan-1602-Jan-16 01-Jan-1610-Jan-16220
04-Jan-1605-Jan-1604-Jan-16 01-Jan-1610-Jan-16220
05-Jan-1605-Jan-1605-Jan-16 01-Jan-1610-Jan-16110
07-Jan-1608-Jan-1607-Jan-16 01-Jan-1610-Jan-16220
08-Jan-1608-Jan-1608-Jan-16 01-Jan-1610-Jan-16110
09-Jan-1610-Jan-1609-Jan-16 01-Jan-1610-Jan-16220

 

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,661
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.

 

Please follow the screenshots below as a reference:


Sample data in my test:

1.PNG

 

Design Surface:

2.PNG

 

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

 Untitled.jpgTimeline Slicer

 

 

Untitled.jpgHierarchy Slicer

 

 

 

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