Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table of incidents that contains the following fields:
name : string
lostservice : timestamp
regainedservice :timestamp
I'd like to be able to allow the user to select a date range using a slider and then show the duration and the duration as a percentage of the total time range for each entry.
I can manage calculate duration quite easily for the divident, but I'm not sure how to get the start and end dates from the slicer in order to calulate the time span for the divisor
Solved! Go to Solution.
Hi @beerygaz,
Please new a calendar table first which is unrelated with the actual data table. Later, you should add [Date] field from this table into slicer.
Date = CALENDAR(MIN(data[lostservice]),MAX(data[regainedservice]) )
Create measures similar to below.
date range = VAR startdate = MIN ( 'Date'[Date] ) VAR enddate = MAX ( 'Date'[Date] ) RETURN DATEDIFF ( startdate, enddate, DAY ) + 1 percentage = VAR startdate = MIN ( 'Date'[Date] ) VAR enddate = MAX ( 'Date'[Date] ) VAR lostdate = SELECTEDVALUE ( data[lostservice] ) VAR regaineddate = SELECTEDVALUE ( data[regainedservice] ) RETURN IF ( lostdate >= startdate && regaineddate <= enddate, DATEDIFF ( lostdate, regaineddate, DAY ), IF ( lostdate >= startdate && lostdate <= enddate && regaineddate >= enddate, DATEDIFF ( lostdate, enddate, DAY ), IF ( lostdate < startdate && regaineddate <= enddate && regaineddate >= startdate, DATEDIFF ( startdate, regaineddate, DAY ), IF ( lostdate < startdate && regaineddate > enddate, DATEDIFF ( startdate, enddate, DAY ), BLANK () ) ) ) ) / [date range]
Best regards,
Yuliana Gu
Hi @beerygaz,
Please new a calendar table first which is unrelated with the actual data table. Later, you should add [Date] field from this table into slicer.
Date = CALENDAR(MIN(data[lostservice]),MAX(data[regainedservice]) )
Create measures similar to below.
date range = VAR startdate = MIN ( 'Date'[Date] ) VAR enddate = MAX ( 'Date'[Date] ) RETURN DATEDIFF ( startdate, enddate, DAY ) + 1 percentage = VAR startdate = MIN ( 'Date'[Date] ) VAR enddate = MAX ( 'Date'[Date] ) VAR lostdate = SELECTEDVALUE ( data[lostservice] ) VAR regaineddate = SELECTEDVALUE ( data[regainedservice] ) RETURN IF ( lostdate >= startdate && regaineddate <= enddate, DATEDIFF ( lostdate, regaineddate, DAY ), IF ( lostdate >= startdate && lostdate <= enddate && regaineddate >= enddate, DATEDIFF ( lostdate, enddate, DAY ), IF ( lostdate < startdate && regaineddate <= enddate && regaineddate >= startdate, DATEDIFF ( startdate, regaineddate, DAY ), IF ( lostdate < startdate && regaineddate > enddate, DATEDIFF ( startdate, enddate, DAY ), BLANK () ) ) ) ) / [date range]
Best regards,
Yuliana Gu
Hi there
What you can do in your measure is the following for the min and max dates
The measure below will give you the total days based on what you selected on your Slicer
My Measure = VAR MinDate = MIN(Table[lostservice]) VAR MaxDate = MAX(Table[lostservice]) VAR TotalDays = DATEDIFF(MinDate,MaxDate,Day) RETURN TotalDays