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