Date column comparison based on Quarter

[krishnavzm]

Hi All,

I'm new to PowerBI. I'm trying to compare two date coumns with Quarter no's. Please see the below table. I got the input as Actual Arrival Date and Expected Arrival Date. Then i can get the Quarter for the both the date.

Now My question is how can i get the Flag value and apply color coding for each case with Power BI Desktop

If Quarter-Actual Arrival Date = Quarter- Expected Arrival Date, Then Flag =1  - Green

If Quarter-Actual Arrival Date > Quarter- Expected Arrival Date, Then Flag =2 - Red

If Quarter-Actual Arrival Date < Quarter- Expected Arrival Date, Then Flag =3 - Dark Green

Actual Arrival Date	Quarter-Actual Arrival Date	Expected Arrival Date	Quarter- Expected Arrival Date	Flag
2021-05-18	Q2	2021-05-26	Q2	1
2021-04-16	Q3	2021-03-12	Q1	2
2021-07-06	Q3	2021-10-18	Q4	3

2021-07-01               Q3                                         2022-07-18                       Q3                                                 3

[v-zhangti · Accepted solution]

Hi, @krishnavzm 

You can try the following methods.
Column:

Actual YQ = YEAR([Actual Arrival Date])&FORMAT([Actual Arrival Date],"q")

Expected YQ = YEAR([Expected Arrival Date])&FORMAT([Expected Arrival Date],"q")

Change the data format of the two columns from text to number.

Measure:

Flag = SWITCH(TRUE(),
SELECTEDVALUE('Table'[Actual YQ])=SELECTEDVALUE('Table'[Expected YQ]),1,
SELECTEDVALUE('Table'[Actual YQ])>SELECTEDVALUE('Table'[Expected YQ]),2,
3)

Color = SWITCH(TRUE(),
[Flag]=1,"Green",
[Flag]=2,"Red",
[Flag]=3,"Dark Green")

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

try to add a new column with the code below:

Flag =

IF (

    Data[Quarter-Actual Arrival Date] = Data[Quarter- Expected Arrival Date],

    1,

    IF (

        Data[Quarter-Actual Arrival Date] > Data[Quarter- Expected Arrival Date],

        2,

        3

    )

)

For the conditional coloring, please refer to this:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

[krishnavzm]

This will not work when we compare dates with 12/12/2021 and 12/12/2022, in my case both will come as Q4 and Q4 . So instead of value 3, I'm getting as 1.

[FreemanZ]

you can enhance the quarter columns, e.g. by changing Q2 to 2021Q2 or 2022Q2, depending on the date column. The code shall still work.