Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to create Dynamic Date-Time Range

Hello All,   I have been struggling with the below query from last few days.   I am trying to create a dynamic Date and Time ranges selection as per the user needs.   In my table i have swipe i...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Seward12533 I tried a different and easy way to do this.

     

    1. Break relationship from date range table to 'May' table.

    2. Write a measure to check current selection from date range table and return tag.

    Is Range =
VAR currDate =
    MAX ( May[Date-Time] )
VAR _start =
    MAX ( DateRange[SelectedDate-Time] )
VAR _end =
    MAX ( DateRange[NextDate] )
RETURN
    IF ( currDate >= _start && currDate <= _end, "Y", "N" )

     

    3. Create table visual based on 'May' table, drag above measure to visual level filter to filter matched records.

     

     

    and then i have written measures to calculate the max and min times and the duration.

     

    Min = CALCULATE(
	MIN('May'[Date-Time]),
		ALLSELECTED(May)	
)

Max = CALCULATE(
	MAX('May'[Date-Time]),
		ALLSELECTED(May)	
)

     

    It worked for me.

Seward12533's avatar
Seward12533
Solution Sage
8 years ago

Thanks but I don't see any data model, measures etc and the DateRange table all has values of -1 and its all one employee and the begin and end date times were all 5:#0am. Regardless took a stab at it. Took the liberty making some assupmptions with your data to make it look more like the sample you provided in the screenshot with one Date, one Time column and rows for In/Out and then calculated a time value by merging Date and Time. 

 

Approach

  • Needed to use disconected slicers for Date range and then Start Time and End Time respectively
  • Measures to harvest and build FirstDateTimeSelected adn LastDateTimeSelected
  • Build Date/Time Table - see this post for cool M scripts for dynamic date/time table in powerquery
  • Use measures with date filtering to calcualte statistics within the range selected

 

Note - this woudl be much easier if power bi supported Date/Time Slicers - Please review this feature request and vote for it!

 

Link to Workbook https://1drv.ms/u/s!AuCIkLeqFmlhhJkJLISRR4DlYhwgHQ

 

Selected Start Time = SELECTEDVALUE(TimeRangeStart[Time Start],TIME(0,0,0)) // default to Midnight
Selected End Time = SELECTEDVALUE(TimeRangeEnd[Time End],TIME(23,30,30))  // default to 11:30pm last available choice should It be 23,59,59
FirstDateSelected = MIN(DateRange[Date])
LastDateSelected = MAX(DateRange[Date])
FirstDateTimeSelected = [FirstDateSelected]+[Selected Start Time]
LastDateTimeSelected = [LastDateSelected]+[Selected End Time]

Hours = SUM(New[Minutes])/60

Selected Hours = CALCULATE([Hours],FILTER(DimDateTime,DimDateTime[DateTime]<=[LastDateTimeSelected]&&DimDateTime[DateTime]>=[FirstDateTimeSelected]))

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Seward12533 I tried a different and easy way to do this.

 

1. Break relationship from date range table to 'May' table.

2. Write a measure to check current selection from date range table and return tag.

Is Range =
VAR currDate =
    MAX ( May[Date-Time] )
VAR _start =
    MAX ( DateRange[SelectedDate-Time] )
VAR _end =
    MAX ( DateRange[NextDate] )
RETURN
    IF ( currDate >= _start && currDate <= _end, "Y", "N" )

 

3. Create table visual based on 'May' table, drag above measure to visual level filter to filter matched records.

 

 

and then i have written measures to calculate the max and min times and the duration.

 

Min = CALCULATE(
	MIN('May'[Date-Time]),
		ALLSELECTED(May)	
)

Max = CALCULATE(
	MAX('May'[Date-Time]),
		ALLSELECTED(May)	
)

 

It worked for me.