calculated column for FED FY

[Anonymous]

Hello All-----I have a Column titled "Date Value" and it has various dates in this specific format: 8/1/2020 (M/DD/YYY)

I would like to create a new column titled "Fiscal Year." I need DAX formula for this column so that it will look in the "Date Value" Column and if there is a month that begins with:

10, 11, 12 it will return "1st Qtr" in the new "Fiscal Year"column

1, 2, 3 it will return "2nd Qtr" in the new "Fiscal Year" column

4, 5, 6 it will return "3rd Qtr" in the new "Fiscal Year" column

7, 8, 9 it will return "4th Qtr" in the new "Fiscal Year" column

Thanks for any help!

[Anonymous]

Thanks----when I use the whole script like you typed it, I get a syntax error. However when I use your example for just 1 month, like the first line (=1, 2nd Qtr) it works perfect. Is there commas or anything else missing in order for the whole expression with all months like you have it to work?

[amitchandak]

@Anonymous , While you can use the solution posted first, below. I would suggest to use a calendar table. Please find the calendar table for Oct-Sep after signature.

[littlemojopuppy]

@amitchandak In all seriousness thank you for at least acknowledging that another solution was previously offered.

For what it's worth, I'd go with your solution of putting the quarter in the date table.  @Anonymous  you really should do that.  Makes it much easier.

[amitchandak]

@littlemojopuppy , Since only few days back the last solution starting to come on top. That means if user does not scroll and if see a solution on top, which is working, he will take that; and first soluion will not get credit. I realized that. I and trying do same whenever I post second or after. But if it is parallel reply, I might miss.

[littlemojopuppy]

@amitchandak I've noticed that too and honestly not a fan.

But you can see that a solution has been offered.  You think it's totally wrong, have at it.  We all want to help.  AiB offered a solution today at basically the same time I offered one I told the user to use hers because it was eloquent...so much better than what I offered.

But you posting links to the exact same articles, offering solutions that are virtually identical to previously offered solutions is not cool.  Multiple threads on the same topic most likely confuses users and it shows no respect to others who are trying to help the users.

@Greg_Deckler 

[littlemojopuppy · Accepted solution]

Hi @Anonymous 

Try this out...

SWITCH(
	TRUE(),
	MONTH([Date Value]) = 1, "2nd Qtr"
	MONTH([Date Value]) = 2, "2nd Qtr"
	MONTH([Date Value]) = 3, "2nd Qtr"
	MONTH([Date Value]) = 4, "3rd Qtr"
	MONTH([Date Value]) = 5, "3rd Qtr"
	MONTH([Date Value]) = 6, "3rd Qtr"
	MONTH([Date Value]) = 7, "4th Qtr"
	MONTH([Date Value]) = 8, "4th Qtr"
	MONTH([Date Value]) = 9, "4th Qtr"
	MONTH([Date Value]) = 10, "1st Qtr"
	MONTH([Date Value]) = 11, "1st Qtr"
	MONTH([Date Value]) = 12, "1st Qtr"
)

Hope this helps!  🙂 

[Anonymous]

I figured it out using your solution, many thanks-----works perfect!

[Anonymous]

I think I just figured it out, I will report back once finished.