Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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,

 

CreatedEntry_Date
11/10/2021 16:2311/10/2021
11/10/2021 17:2311/10/2021
11/10/2021 18:2311/10/2021
11/10/2021 18:2711/10/2021
11/10/2021 19:1011/10/2021
11/10/2021 19:3011/10/2021
11/11/2021 0:3011/10/2021
11/11/2021 0:3511/10/2021
11/11/2021 0:4511/10/2021
11/11/2021 1:3011/10/2021
11/11/2021 2:3011/10/2021
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

     

    Try this code to add a new column to your table:

    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.
    Appreciate your Kudos!!


    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous 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))
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Anonymous 

     

    Try this code to add a new column to your table:

    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.
    Appreciate your Kudos!!


    LinkedIn:     www.linkedin.com/in/vahid-dm/