How do I group hire date by fiscal year and then add comment?

[Newbie22]

Hi Everyone, I hope y'all enjoyed the holidays 😄

I'd like to ask for your help and give me tips on how to group hire dates by fiscal year.

I have a "Country Hire Date" column and I need to group them by Fiscal Year. We're using state fiscal year so that's July 1 through June 30 of the following year,and is named for the calendar year in which it ends (e.g., July 1, 2021 through June 30, 2022 is FY22).

Once FY is identified, I also need to insert comment per line. Below are the comments and descriptions:

Comments	Description
Joined less than 1 year	Country Hire Date is within the current fiscal year
Joined 1st fiscal year	Country Hire Date is previous fiscal year/equal to 1 fiscal year
Joined 2nd fiscal year	Country Hire Date is equal to 2 previous fiscal years

Here's the output I needed:

Name	Country Hire Date	Fiscal Year	Comments
A	1/4/2021	FY21	Joined 2nd fiscal year
B	12/18/2021	FY22	Joined 1st fiscal year
C	10/7/2021	FY22	Joined 1st fiscal year
D	7/14/2022	FY23	Joined less than 1 year
E	9/20/2022	FY23	Joined less than 1 year
F	6/27/2022	FY22	Joined 1st fiscal year
G	8/18/2020	FY21	Joined 2nd fiscal year
H	7/2/2021	FY22	Joined 1st fiscal year
I	11/30/2020	FY21	Joined 2nd fiscal year
J	5/29/2022	FY22	Joined 1st fiscal year

Thank you so much for your help 🤗

[Anonymous · Accepted solution]

Hi @Newbie22 ，

Please try this:

Comments = 
VAR _year1 =
    IF (
        MONTH ( 'Table'[Country Hire Date] ) <= 6,
        YEAR ( 'Table'[Country Hire Date] ) - 1,
        YEAR ( 'Table'[Country Hire Date] )
    )
VAR _year2 =
    IF ( MONTH ( TODAY () ) <= 6, YEAR ( TODAY () ) - 1, YEAR ( TODAY () ) )
VAR _year = _year2 - _year1
VAR _comments =
    SWITCH (
        TRUE (),
        _year = 0, "Joined less than 1 year",
        _year = 1, "Joined 1st fiscal year",
        _year = 2, "Joined 2nd fiscal year"
    )
RETURN
    _comments

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[amitchandak]

@Newbie22 , Create a new column

Diff =

Var _st = year( if(month([Hire Date])<= 6, year([Hire Date]) -1, year([Hire Date])), 7,1)

Var _curr= year( if(month(today())<= 6, year(today()) -1, year(today())), 7,1)

return

datediff(_st, _curr, Year)

Based on value 0,1,2,3 you can use switch statement to have a text column

Switch-Case statement of #PowerBI: https://www.youtube.com/watch?v=gelJWktlR80&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=56