Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.