Conditional Column with values for fiscal year date ranges

[Thag]

Trying to add a column using power query to label fiscal years as FY1, FY2, etc. but when the start date and end dates are in the middle of the month. Example: 9/25/2023-9/24/2024 is FY1

[v-csrikanth]

Hi @Thag 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @Thag 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @Thag 

Thank you for being part of the Microsoft Fabric Community.

As highlighted by @tharunkumarRTK , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

Best Regards,
Community Support Team _ C Srikanth.

[danextian]

Hi @Thag 

but when the start date and end dates are in the middle of the month? Your statement seems to be lacking. can you  please give us a full example? What about for the other dates?

[tharunkumarRTK · Accepted solution]

@Thag 

Table.AddColumn(TableName, "FiscalYearLabel", each 
         if [StartDate] >= #date(2023,9, 25  ) && [EndDate] <= #date(2024,9,24 ) then
            "FY1" else ""
    )

Incase if your financial year calculation invlolves any logic then please share it here to help you better.

For example if your financial year depends on current year then 

let
    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
    

    FiscalStartFY1 = #date(2023, 9, 25),
    

    AddFY = Table.AddColumn(Source, "FiscalYearLabel", each 
        let
            CurrentDate = [YourDateColumn],
            FiscalYearNumber = Number.IntegerDivide(Duration.Days(CurrentDate - FiscalStartFY1), 365) + 1
        in
            "FY" & Text.From(FiscalYearNumber)
    )
in
    AddFY

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!