Forum Discussion
If a date falls between dates, return a value
Welcome to PowerBI! There are several ways to go about this depending on your use case. It appears you are currently in the Transform Data or Table section of PowerBI.
If you are simply needing a table with the data sorted; You can add a table visual to the Reporting view. Once you do this you can use the native sort feature to sort by the date or FY column.
If you need the data sorted by FY on the table view, you can also sort on that view by right clicking the column header and sorting there.
If you only want the year in the FY column; You could select "Create a Column from Examples" in the Transform Data section and simply give it a few of the "Correct" years in the example rows and it will automatically add a column with the appropriate rules to extract the correct year from the "FY16" and beyond values.
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.
And just noticed it looks like you don't have the FY values created. You can create a calculated COLUMN on the data table and insert this measure. (Update dates to be the Month End Date it appears?)
FY = CONCATENATE("FY",RIGHT(YEAR(Dates[Date]),2))