Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bhoobala_P
Frequent Visitor

Need to find date range using timeline slicer filter

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 InputTimeline 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%).

 

 

6 REPLIES 6
v-yulgu-msft
Employee
Employee

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.
Anonymous
Not applicable

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 .

 

 

 

 

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.

 

 

 

 

Anonymous
Not applicable

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

 

 

 

 

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 SlicerTimeline Slicer

 

 

Hierarchy SlicerHierarchy Slicer

 

 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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