Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have an issue with an IF formular as I do not now how i can build the if formular in Power BI as I am quite new in Power BI.
I have two different columns from two data fields with a date. Now I want to compare the dates.
My Idea: IF both dates match than it would be "on Time".
Otherwise it should be saying : too late or too early depends on the date.
In Excel my formular would be:
IF(Delivery Appointment DATE=Event Date NEW UTC ;"On Time";IF(Delivery Appointment DATE<Event Date NEW UTC;"Early";"Late"))
Thanks and best regards
Solved! Go to Solution.
Hi @SN1900
Please see the below.
Column = VAR x = ( CALCULATE( MAX( Event[Event Date NEW UTC] ), Event[STATUS] = "DELIVERED" ) - Delivery[Delivery Appointment DATE] ) * 1 RETURN SWITCH( TRUE, x = 0, "On Time", x > 0, "Late", "Early" )
Hi @SN1900
Please see the below column expression.
Column = SWITCH( TRUE(), 'Table'[Delivery Appointment DATE] = 'Table'[Event Date NEW UTC], "On Time", 'Table'[Delivery Appointment DATE] > 'Table'[Event Date NEW UTC], "Late", "Early" )
Result.
Hi Mariusz,
thanks for your fast response!
I tried your formular by adding the new column with the formular in the dataset shipment Analysis.
Therefore I could only select the "Delivery Appointment" data as dropdown and I had too enter in the "Event data new utc" manually as this data comes from another field.
Now I reiceved this error message saying:A single value for column "event data new utc" in table "otm event status cannot" be determined.
Just for your information.
It is possible that there are more event dates but only one delivery appointment date. could that be the issue?
Many thanks and best regards
Niclas
Hi @SN1900
Sorry the solution I gave you works on the assumption that the data was in one table, what is the relationship between this tables.
Hi Mariusz,
The relationship between the tables is a shipment ID number.
There is one Delivery Appointment date. So one ID number has one delivery appointment date.
But every ID nummber can have multiple event date based on the Status code.
I want to compare the Delivery appointment date only with the Event date that has the Status code delivered to see which ones are delivered on time and which not.
Hi @SN1900
Please see the below.
Column = VAR x = ( CALCULATE( MAX( Event[Event Date NEW UTC] ), Event[STATUS] = "DELIVERED" ) - Delivery[Delivery Appointment DATE] ) * 1 RETURN SWITCH( TRUE, x = 0, "On Time", x > 0, "Late", "Early" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |