Forum Discussion
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 .
kris, your solution had solved half of it. however issues persists..!
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.