topic Re: calculated column for FED FY in DAX Commands and Tips

calculated column for FED FY

Anonymous — Fri, 08 Jan 2021 18:04:21 GMT

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!

Re: calculated column for FED FY

littlemojopuppy — Fri, 08 Jan 2021 19:24:52 GMT

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! 🙂

Re: calculated column for FED FY

amitchandak — Sat, 09 Jan 2021 01:50:28 GMT

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.

Re: calculated column for FED FY

littlemojopuppy — Sat, 09 Jan 2021 01:57:22 GMT

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.

Re: calculated column for FED FY

amitchandak — Sat, 09 Jan 2021 02:08:04 GMT

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.

Re: calculated column for FED FY

littlemojopuppy — Sat, 09 Jan 2021 02:20:03 GMT

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

Re: calculated column for FED FY

Anonymous — Mon, 11 Jan 2021 15:15:05 GMT

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?

Re: calculated column for FED FY

Anonymous — Mon, 11 Jan 2021 15:22:10 GMT

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

Re: calculated column for FED FY

Anonymous — Mon, 11 Jan 2021 15:34:06 GMT

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