Forum Discussion
If a date falls between dates, return a value
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:
10 Replies
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 CIteration 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.
Use your table name place of TableName :-
Try this out if you want to return 0 or 1Final = VAR _Date = [Date] VAR Result = IF(TableName[StartDate] <= _Date && TableName[EndDate] >= _Date , 1, 0) ) RETURN ResultIf you want to return FY value :-
Final =
VAR _Date = [Date]
VAR Result =
IF(TableName[StartDate] <= _Date && TableName[EndDate] >= _Date , TableName[FY])
)
RETURN
ResultThanks,
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
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.
