Finding Latest Fiscal Date in Column

[ABR002]

Hi, so what I need to do is I need to have a card that displays the latest fiscal date in a column. My companies Fiscal Year runs from Jul-Jun:

Calendar Date	Fiscal Month No	Fiscal Year
7/1/2021	1	2022
8/1/2021	2	2022
9/1/2021	3	2022
10/1/2021	4	2022
11/1/2021	5	2022
12/1/2021	6	2022
1/1/2022	7	2022
2/1/2022	8	2022
3/1/2022	9	2022
4/1/2022	10	2022
5/1/2022	11	2022
6/1/2022	12	2022

I have a file that shows expenses based on a date, but what I need to somehow extract the date (in a MMMM YYYY format) of the latest one and put it on a card to show "numbers as of" which would be the last date. The problem is that when I use a max function, it automatically goes to December, as it thinks that December 2022 is after January 2022.

Here's an example of my data:

Date	Expense	Fiscal Year	Fiscal month	Concat	Need
7/4/2021	$45	2022	1	1/2022	Jul-22
12/4/2021	$44	2022	6	6/2022	Dec-22
1/4/2022	$16	2022	7	7/2022	Jan-22
1/6/2022	$11	2022	7	7/2022	Jan-22
2/6/2022	$10	2022	8	8/2022	Feb-22
4/5/2022	$33	2022	10	10/2022	Apr-22

And the desired result would be to extract from need and get "April 2022". It just seems like everything I try has a side effect.

[ebeery · Accepted solution]

@ABR002 What have you tried?  What are the side effects?

From my testing, it seems like you could just use MAX(Table[Date]) and set the format string on the measure to MMMM YYYY.

[ABR002]

For some reason it always gives me December 2021 when I do that. I can't figure out why. The latest date is July 2021, but it gets more complicated (then I originally mentioned) as the months aren't calendar months, so April 1 might actually be in March.

What I ended up doing is creating a conditional column that sends the fiscal years date back a year if the month is less than or equal to 6, combine the year and month, and then filter that by max.