09-28-2016 11:22 PM - edited 09-28-2016 11:25 PM
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:
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:
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|
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|
and the resultant period is:
|Eq.Delivery Date||Eq.Collection Date||No of days active||Comments|
|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|
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%).
09-29-2016 03:22 AM
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 .
09-29-2016 04:36 AM - edited 09-29-2016 04:51 AM
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 (%)|
PS: Creation Date is used for the time slicer. Difference between Delivery Date and Collection Date is needed.
09-29-2016 05:22 AM
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])
09-30-2016 04:22 AM
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:
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
09-30-2016 04:25 AM
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..!!
10-05-2016 05:43 AM
Very strange. I'm not sure but maybe if you remove the ALLSELECTED filter from your calculations..? Custom visuals can't always be trusted.