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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Seward12533 thanks for the reply.

 

  • Try making a date-time table to join vs a date-table it may simplify things. This will let you slice on Date/Time.

I tried this. but i cant create relashionship with NEW table because there lots of duplicated values are getting generated as if you see that for each date there are 48 time ranges. so i cant go with this.

 

I have already written dax's to get the MAX of Last out and Min of First IN and And the Spent hours(Last out-First in by DateDIff function).

 

For the third point, i didnt get what you are trying to suggest

 

 

Seward12533's avatar
Seward12533
Solution Sage
8 years ago
I don’t understand why the date-date table will not work as a bridge table. By definition there is only one value for each time so it has to be unique.

Can you share a model with some representative data? (Upload to a drop box or onedrive and Share link)
    • 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.