Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Time-by-date measures

Good day to all

I would like to know if there is any way to change a date according to the time, that is, example:

I have a database where the date and time information is from 01/16/2023 to 14+20, so you must stay with that date

But if I get a case on the same 16/01/2023 at 15 + 02 it must be dated the next working day, that is, 01/17/2023 or in case it is a weekend (Saturday, Sunday or holiday), it is dated the next working day.

Someone has knowledge and can help me

6 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    Hi Syndicate_Admin 

     

    Can you please try a calculated column like this:

     

    NewDate = 

VAR __LeapTime = TIME(15,2,0) -- Please define leaptime

VAR __DayAndTime = [Column1]
VAR __Day = ROUNDDOWN([Column1],0)
VAR __Time = TIME(HOUR([Column1]),MINUTE([Column1]),0)

Return

IF(__Time < __LeapTime,__Day,__Day+1)

     

     

    Best regards
    Michael
    -----------------------------------------------------
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Appreciate your thumbs up!
    @ me in replies or I'll lose your thread.

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    Hello @SoniaH0809_ !

    I think one of the ways to do that is by creating the time ranges and through the conditions to create the new column.

    Here is a very simple example.

    If you are extracting data from a bank, you can do the condition directly in the SQL statement. (translated by google)

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      Hello marcelsmaglhaes

      I would like to ask you an additional question, if there is a possibility that at the time that exceeds the limit of 15 + 00 change not only the day to the next but also place it with time of 8 a.m and the new date according to the measurement

      I don't know if you knew how to make that change

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      @SoniaH0809_ ,

      Yes, it is possible.

      But now thinking not much, I think you can use the #datetime (#datetime - PowerQuery M | Microsoft Learn). See that you have to break the new date generated in year, month and day, and add 8 to 8:00.

      I don't know if you could understand

      And taking advantage of the fact that the creation of columns should be done in Power Query and not in DAX (of course each case is a case, but data transformation = Power Query). (Translated by Google)

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Administrator
        3 years ago

        Hello marcelsmaglhaes

        Thank you very much and apply it in my exercise and effectively the measure function,

        Thank you!!!