Forum Discussion

Bhoobala_P's avatar
Bhoobala_P
Frequent Visitor
9 years ago

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

 

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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 .

     

     

     

     

    • Bhoobala_P's avatar
      Bhoobala_P
      Frequent Visitor
      9 years ago

      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 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's avatar
        Anonymous
        Not applicable
        9 years ago

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

         

         

         

         

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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:

     

    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