DAX query required for IF and IF Else condition of below case

[Ayyappa5678]

Hi Team,

I am new to PBI, help me with this case!

IF type(col) = "AAA" & Date time(col) < 11AM then Pickup date = Date(col)-1
ELSE Pickup date = Date(col)

IF type(col) = "BBB" then Pickup date = Date(col)–1

IF type(col) = "CCC" then Pickup date = Date(col)

Value of "AAA" is having AAA_a, AAA_b, AAA_c, AAA_d, AAA_e ----AAA_n etc. So here we are calling the condition with "AAA". How do we calling "AAA" with one IF condition statement or any other.

Same like value of  "BBB" & Value of "CCC".

Here date time(col) contains 24hrs, we required less than(< )upto 11AM then Pickup date = Date(col)-1, else Pickup date = Date(col).
Here Date(col) is calculated column.

So, kindly helpe me with the DAX query. below is the snapshot for your reference.

[BeaBF]

@Ayyappa5678 Hi! Here an example code:

Pickup date =
VAR TypeCol = [type(col)]
VAR ColDateTime = [datetime(col)]
VAR TimeOfDay = TIME(HOUR(ColDateTime), MINUTE(ColDateTime), SECOND(ColDateTime))

RETURN
SWITCH(
TRUE(),
TypeCol = "AAA" && TimeOfDay < TIME(11, 0, 0), DATEVALUE(ColDateTime) - 1,
TypeCol = "AAA" && TimeOfDay >= TIME(11, 0, 0), DATEVALUE(ColDateTime),
TypeCol = "BBB", DATEVALUE(ColDateTime) - 1,
TypeCol = "CCC", DATEVALUE(ColDateTime)
)

if you provide sample data and more info, i can help you better.

BBF

[Ayyappa5678]

Hi,
Thanks for your response. Below is the sample data is thier with snapshot. kindly help me.
1. here I have multiple "AAA" , "BBB" values and help me with the solution.

2. Time values are in decimal values.
@BeaBF

[BeaBF]

@Ayyappa5678 copy and paste here data, thx

[Ayyappa5678]

type           date             time

AAA_a	2/22/2024	10.8
AAA_b	2/26/2024	4.75
AAA_c	1/16/2024	22.7
BBB_a	2/22/2024	15.8
BBB_b	2/12/2024	2.5
BBB_c	1/12/2024	23
BBB_d	1/29/2024	1.08
CCC	2/22/2024	0.9

Hi,
Thanks for your response. Below is the sample data is thier with snapshot. kindly help me.
1. here I have multiple "AAA" , "BBB" values and help me with the solution.

2. Time values are in decimal values.

[BeaBF]

@Ayyappa5678 here the correct calculated column:

Pickup date =

VAR TypeCol = [type]

VAR DateCol = [date]

VAR TimeCol = [time]

VAR TimeAsFraction = TimeCol - INT(TimeCol)

VAR TimeAsTime = TIME(INT(TimeCol), INT(TimeAsFraction * 60), 0)

VAR DateTimeCol = DateCol + TimeAsTime

RETURN

    SWITCH(

        TRUE(),

        LEFT(TypeCol, 3) = "AAA" && TimeAsTime < TIME(11, 0, 0), DateCol - 1,

        LEFT(TypeCol, 3) = "AAA" && TimeAsTime >= TIME(11, 0, 0), DateCol,

        LEFT(TypeCol, 3) = "BBB", DateCol - 1,

        TypeCol = "CCC", DateCol,

        DateCol

    )

BBF

[BeaBF]

@Ayyappa5678 accept the message as a solution.

[Ayyappa5678]

Hi,
Thanks for your response. everything working is fine.
But, date = blank, then getting the below snapshot of 12/29/1899 year date. kindly pls let me know how to change with 12/29/1899 year date to blank rows.

@BeaBF 

[BeaBF]

@Ayyappa5678 accept my answer as a solution, please. Then, i didn't understand the problem with the date, can you try to explain me?

BBF

[Ayyappa5678]

Hi,

Currently my Date column having "12/29/1899", how to change it with "blank" records. kindly help me with this pls. like below snapshot.
      Date            Updated_Date

12/29/1899
12/29/1899
12/29/1899
12/29/1899
12/29/1899

[BeaBF · Accepted solution]

@Ayyappa5678 Here:
Updated_Date = IF([Date] = DATE(1899, 12, 29), BLANK(), [Date])

but if the measure is correct, accept the message as solution.

BBF