Forum Discussion

krishnavzm's avatar
krishnavzm
Frequent Visitor
3 years ago
Solved

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 t...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

     

krishnavzm's avatar
krishnavzm
Frequent Visitor
3 years ago

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's avatar
    FreemanZ
    Super User
    3 years ago

    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.