Forum Discussion
Date column comparison based on Quarter
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
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.
4 Replies
FreemanZSuper User
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: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.
v-zhangtiCommunity Support
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.
