cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SN1900
Frequent Visitor

IF formular

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")) Power BI.PNG

Thanks and best regards 

 

1 ACCEPTED 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"
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Why are you doing this in DAX instead of in Power Query, where this calculation truly belongs?

Best
Darek
Mariusz
Super User II
Super User II

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.

image.png

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

SN1900
Frequent Visitor

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?

 

power bi 2.PNG

 

 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.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski


 

SN1900
Frequent Visitor

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. 

 

power bi 4.PNG

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"
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors