Forum Discussion

Ivolution93's avatar
Ivolution93
Frequent Visitor
6 years ago
Solved

DAX If statement help

I am wanting it to give me the result of a date minus one day if it is before 6:30 in the morning else use just the result of a date.

 

DateShiftStarted =
if (WBoxCountTransactions[TimePoint] < "06:30:00",
    WBoxCountTransactions[Date Box Created] - 1,
   WBoxCountTransactions[Date Box Created]
    )
 
i am however getting the following message
DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
 
I have checked both columns used in the dax and one is TIME and another is DATE i dont have a value of text
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Ivolution93 

    try like

    if (WBoxCountTransactions[TimePoint] < time(06,30,00) ,
    WBoxCountTransactions[Date Box Created] - 1,
    WBoxCountTransactions[Date Box Created]
    )

     

    or

     

    if (WBoxCountTransactions[TimePoint].time < time(06,30,00) ,
    WBoxCountTransactions[Date Box Created] - 1,
    WBoxCountTransactions[Date Box Created]
    )

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Ivolution93 

    try like

    if (WBoxCountTransactions[TimePoint] < time(06,30,00) ,
    WBoxCountTransactions[Date Box Created] - 1,
    WBoxCountTransactions[Date Box Created]
    )

     

    or

     

    if (WBoxCountTransactions[TimePoint].time < time(06,30,00) ,
    WBoxCountTransactions[Date Box Created] - 1,
    WBoxCountTransactions[Date Box Created]
    )

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    If this is for a calculated column, you can use the TIME function instead of putting the time in quotes.

     

    DateShiftStarted =
    if (WBoxCountTransactions[TimePoint] < TIME(6,30,0),
        WBoxCountTransactions[Date Box Created] - 1,
       WBoxCountTransactions[Date Box Created]
        )
     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Ivolution93 ,

    According to my understanding, you want to calculate the Date based on Time  , right?

    You could use TIMEVALUE() to compare time ,like the following formula:

    Column =
IF (
    WBoxCountTransactions[TimePoint] < TIMEVALUE ( "6:30 AM" ),
    WBoxCountTransactions[Date Box Created].[Date] - 1,
    WBoxCountTransactions[Date Box Created]
)

    My visualization looks like this:

    Is the result what you want? If you have any questions, please upload some data samples and expected output.

    Please do mask sensitive data before uploading.

     

    Best Regards,

    Eyelyn Qin

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Ivolution93 ,

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin