Forum Discussion
Finding Latest Fiscal Date in Column
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.
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.
2 Replies
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.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.
