Forum Discussion
If a date falls between dates, return a value
I'm trying to create a column for the FY so that I can sort by FY in my visuals. Ideally, the column would return FY22, FY23, etc based on the Date_Onboard. I think creating a column from examples would be a pain as I would need to find specific items that happen to have dates at the farthest ranges of the fiscal years to sort by, correct?
It would remove the FY with a replace "FY" with "" so you wouldn't have to go through the iterations. That being said it doesn't seem like that is the route to go regardless if you want the FY in the column.
Overall, I don't think you need any additional Steps. By adding the FY field to the visual it will allow you to sort by the FY as is. Ascending (Oldest Year first) or Descending (Newest Year first).
PowerBI natively supports sorting by text columns. IF you perhaps have some older years that show FY9 ; you would need to update those rows to include an FY09 to work accordingly. Outside of that, you should be good to add the FY field to your visuals and you would have the ability to sort by that field. IF you need to filter by that field, you can add that field to a Filter visual or to the native powerBI filters.
Yes, the issue is I don't have a FY column. That is what I am trying to create. Here is the table it is on. I have tried several formulas to give me the FY in the FY column and it just is not working. I can get a result for >= 10/1/2021, but not for < 10/1/2022. If I could reference the above table somehow and use that to pull the dates, that would be fine too.