cancel
Showing results for
Did you mean:
Helper III

## Help with shift mapping by date and time

Hello,

Could somebody help me with measure which should produce "Entry_Date" as the earliest date between 2 dates with time between 15:00 PM to next day 03:00 AM from "Created".

Thanks,

 Created Entry_Date 11/10/2021 16:23 11/10/2021 11/10/2021 17:23 11/10/2021 11/10/2021 18:23 11/10/2021 11/10/2021 18:27 11/10/2021 11/10/2021 19:10 11/10/2021 11/10/2021 19:30 11/10/2021 11/11/2021 0:30 11/10/2021 11/11/2021 0:35 11/10/2021 11/11/2021 0:45 11/10/2021 11/11/2021 1:30 11/10/2021 11/11/2021 2:30 11/10/2021
1 ACCEPTED SOLUTION
Super User

``````Column =
Var _Time = TIMEVALUE([Created])
Var _S = time(15,00,00)
Var _E = time(3,0,0)
return
format(if(_Time<_E,DATEVALUE([Created])-1,DATEVALUE([Created])),"MMMM DD")``````

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

3 REPLIES 3
Super User

``````Column =
Var _Time = TIMEVALUE([Created])
Var _S = time(15,00,00)
Var _E = time(3,0,0)
return
format(if(_Time<_E,DATEVALUE([Created])-1,DATEVALUE([Created])),"MMMM DD")``````

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Super User

@chandrashekarc Maybe:

``````Measure =
VAR __Created = MAX('Table'[Created])
VAR __CreatedDate = DATE(YEAR(__Created),MONTH(__Created),DAY(__Created))
VAR __MinDate = __CreatedDate + 3/24 + 1/24/60
VAR __MaxDate = __CreatedDate + 1 + 3/24
VAR __Min = MINX(FILTER(ALL('Table'),[Created]>=__MinDate && [Created]<=__MaxDate),[Created])
RETURN
DATE(YEAR(__Min),MONTH(__Min),DAY(__Min))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Helper III

Not sure, where is the issue as am not seeing the expected results. 2nd column "Login_Date" is the expected output, while i used the given DAX in 3rd column "Column"

Thanks