DAX If statement help

[Ivolution93]

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

[Anonymous]

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

[Anonymous]

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

[mahoneypat]

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

[amitchandak · Accepted solution]

@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]
)