If a date falls between dates, return a value

[IBrant]

I'm new to Power BI and I'm struggling with what seems like a simple task. I need to sort dates by fiscal year. I've tried to do this a couple different ways. 

1. I tried making a differnt column for each FY with this formula but it returns 0 for everything:

FY22 = IF(AND('Auditors'[Date_Onboard].[Date] >= 10/1/2021,'Auditors'[Date_Onboard].[Date] <10/1/2022),1,0)

2. I also tried inporting an excel spreadsheet with the date ranges for each FY and referencing that table to return the FY in each row, but I'm not even sure how to go about this.

[James-ITSavior]

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.

[IBrant]

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?

[James-ITSavior]

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))

[James-ITSavior]

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. 

[IBrant]

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. 

[pratyashasamal]

Hi @IBrant ,
Try this :-

Iteration = 
VAR _Date = [Date] 
RETURN
CALCULATE(
    MAX ( Iterations[FY] ),
    Iterations[StartDate] <= _Date && Iterations[EndDate] >= _Date
)

Thanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[IBrant]

Iteration was not recognized in DAX or M. I also do not want it to calculate the number of FY, I want it to show the FY in the FY column. I will then be able to create many different visuals based on that information. For example, in the FY column, I would like to have FY22, FY23, etc returned based on the date in the Date_Onboard column.

[pratyashasamal]

Use your table name place of TableName :-
Try this out if you want to return 0 or 1

Final =
VAR _Date = [Date] 
VAR Result =
    IF(TableName[StartDate] <= _Date && TableName[EndDate] >= _Date , 1, 0)
)
RETURN
    Result

If you want to return FY value :-

Final =
VAR _Date = [Date]
VAR Result =
IF(TableName[StartDate] <= _Date && TableName[EndDate] >= _Date , TableName[FY])
)
RETURN
Result

Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[IBrant]

This is my table: 

[IBrant]

It isn't giving me an option to refer to another table for the start and end date range to return FY. I need it to look at the "Date Onboard" column and if that date is between 10/1/2021 and 10/1/2022, return FY22, if not return "". OR it can look at the seperate table I pasted above and return the FY if the "Date Onboard" is in the listed ranges on that other table.