CODEWITHSUNDEEP
powerbidax
reborn
reborn

Reputation: 55

Power BI compare dates

I am new with power BI and need your help.

I have 3 columns which are:

  1. date1
  2. reviseDate
  3. Shipped_Date

I need to compare column reviseDate to shipped_date if it is <> or =.

How to I do it, if reviseDate column is blank, must refer on column Date1 to compare with shipped_Date column?

Desired output

Upvotes: 4

Views: 15012

Answers (2)

Andrey Nikolov
Andrey Nikolov

Reputation: 13460

You can define a new calculated column like this:

Status = IF (AND(ISBLANK(Table1[Date1]); ISBLANK(Table1[ReviseDate])); BLANK();
    IF (Table1[Shipped_Date] = IF(ISBLANK(Table1[ReviseDate]); Table1[Date1]; Table1[ReviseDate]); "On Time";
        IF (Table1[Shipped_Date] > IF(ISBLANK(Table1[ReviseDate]); Table1[Date1]; Table1[ReviseDate]); "Late";
            IF (Table1[Shipped_Date] < IF(ISBLANK(Table1[ReviseDate]); Table1[Date1]; Table1[ReviseDate]); "Early"; BLANK())
        )
    )
)

The expression IF(ISBLANK(Table1[ReviseDate]); Table1[Date1]; Table1[ReviseDate]) will return Date1 value in case ReviseDate is empty. Then it is matter of comparing this reference date with Shipped_Date and returning appropriate status.

enter image description here

If you want to make status column using Power Query Editor, you can use Add Column -> Conditional Column. Make one helper column, named ReferenceDate or something, which will compute the date we need to use for comparison:

enter image description here

Then add the actual Status column like this:

enter image description here

The M code will look like this (the data is embedded in it):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7RCQAhDEN36bcUUxTrLOL+aygenCkI/WjymtAxxKp2tQyXJHuunGnDFmFj+HldAQqUGzC89nPlsdYZuiITrL98PRRqQUkYCyqZCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date1 = _t, ReviseDate = _t, Shipped_Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"ReviseDate", type date}, {"Shipped_Date", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "ReferenceDate", each if [ReviseDate] = null then [Date1] else [ReviseDate]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Status", each if [ReferenceDate] = null then "" else if [ReferenceDate] = [Shipped_Date] then "On Time" else if [ReferenceDate] < [Shipped_Date] then "Late" else if [ReferenceDate] > [Shipped_Date] then "Early" else null),
    Status = #"Added Conditional Column1"{2}[Status]
in
    Status

Depending on your data source, you can even modify the query and retrieve this value from the database (e.g. with Transact-SQL query if your data source is SQL Server).

Upvotes: 2

Alexis Olson
Alexis Olson

Reputation: 40264

Try this as a calculated column:

Status = 
VAR DueDate =
    IF(ISBLANK(Table1[Revised]), [Date1], [Revised])
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(DueDate), BLANK(),
        DueDate = Table1[Shipped_Date], "On Time",
        DueDate < Table1[Shipped_Date], "Late",
        DueDate > Table1[Shipped_Date], "Early"
    )

This defines the date you want to compare as a variable that we use to check different conditions.

The SWITCH(TRUE(),...) is a useful construction that returns the specified result for the first condition in the list that evaluates to TRUE().

Upvotes: 4

Related Questions