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

If you can post a link to a sample with some representative data it would help us troubleshoot. 

 

Tips

 

  • Try making a date-time table to join vs a date-table it may simplify things. This will let you slice on Date/Time.
  • Also check out DATEDIFF function - https://msdn.microsoft.com/en-us/query-bi/dax/datediff-function-dax  this will calcuale the total hours between any two date/times. 
  • Using a calculated dax column to lookup the last IN Time for on each OUT Row you can then use DAX to create a table of IN/OUTs (note you can probably replace datetimetable with sampledata in below code and may not need the datetimetable if you use this approach and just your date table linked to the new IN_OUT table to filter based on either In or Out Dates if that is an option for you. 

 

Last IN Column = IF([type]="Out",CALCULATE(MAX(sampledata[Date-Time]),ALL(datetimetable),FILTER(all(datetimetable[date-time]),datetimetable[date-time]<=[date-time]),sampledata[EMP_ID]=[EMP_ID]))
IN_OUT Table = CALCULATETABLE(sampledata),[type]="OUT"
  • you can now add a calculated column with DATEDIFF to canlcuate hours employee worked each shift in the new IN_OUT table. 

 

 

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]))