How to create Dynamic Date-Time Range

[Anonymous]

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 in/out data for each employee.

each employee having their records of their swipe in and swipe out for each day.

Well in every organization there are employees how works at different shift timings.

Currently our organization considering from 6:30 AM(current date) to 6:29:59AM(next date) by defaulty to calculate the spent time by last out-first in

But the problem is , for the  employees who works at shift timings that is for ex 10:00PM(current day) to 10:00 AM(next day) then those employees will be missing their hours spent if the last out was at 9:45AM and first in at 9:50PM,
Because we consider only from 6:30AM to 6:29:59AM.

So get this issue resolve i have came up with a solution to give flexibility to choose their Date and time ranges and check their spent hours for the time range.

Sample Data looks like this

I have a DateMaster Table which having relashioship with begin Date of this table.

Now to choose a date range and time range for a employee from slicer i have created a table by using below power query.

Time Range Table

let
    Source = List.Times(#time(0,0,0) , 1440, #duration(0,0,30,0)),
    convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"TimeRange"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(convertToTable,{{"TimeRange", type time}})
in
    #"Changed Type"

Date Range Table:-

let
    StartDate = #date(StartYear,4,1),
    EndDate = DateTime.Date(DateTime.LocalNow()),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-IN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each TimeRange),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TimeRange"}, {"TimeRange"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"TimeRange", type time}})
in
    #"Changed Type1"

Datemaster Table:-

let
    StartDate = #date(StartYear,4,1),
    EndDate = DateTime.Date(DateTime.LocalNow()),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-IN")
in
    #"Changed Type with Locale"

Now my data model looks like this

Here i can merge the New table and Daterange table with Date and Begin_Date by using left outer join.

But the problem is my data size is around 70lakhs of records.

Then i cant go with merging.

So i need to create a dynamic CALCULATED COLUMN of MEAURE 

as per user selects the date from Date Range column and Time from Time Range column.

As per the selection it should get filtered this Date-Time column.

I.e

SelectedDate-Time = 
VAR maxDate = CALCULATE(MAX('Date'[Date]),ALLEXCEPT('Date','Date'[Date]))
VAR maxTime = CALCULATE(MAX('DateRange'[TimeRange]),ALLEXCEPT('DateRange','DateRange'[Date],DateRange[TimeRange]))
RETURN
    maxDate&" "&maxTime

NextDate = ((DateRange[SelectedDate-Time]+1)-TIME(0,0,1))

I have written these calculated columns in DateRange Table.

Now as per the daterange selection 

the values of Date-Time column values from New table should get filtered.

I have tried the below dax calculated column

Date-Time In Selected Range = 

IF(New[Date-Time] <= VALUES(DateRange[NextDate]) && New[Date-Time] >= VALUES(DateRange[SelectedDate-Time]),New[Date-Time]) 

I tried the measure also

_DateTime In Range = 

IF(VALUES(New[Date-Time]) <= VALUES(DateRange[NextDate]) && VALUES(New[Date-Time]) >= VALUES(DateRange[SelectedDate-Time]),VALUES(New[Date-Time]) )

But both giving the same error

Please suggest me if you have any better solution for this.

I will be soo thankful to you.

Thanks,

Mohan V

[Seward12533]

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]

@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.

• 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. 

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]

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)

[Anonymous]

Hi @Seward12533 please follow the below link for the sample data

https://1drv.ms/x/s!AhiQ2f7YQHC-gbNVIFg5I317_HxqyA

[Seward12533]

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 · Accepted solution]

@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.