Forum Discussion

Thag's avatar
Thag
Regular Visitor
1 year ago
Solved

Conditional Column with values for fiscal year date ranges

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

Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    1 year ago

    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!

5 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Icon for Super User rankSuper User
    1 year ago

    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!
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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?

  • v-csrikanth's avatar
    v-csrikanth
    Icon for Community Support rankCommunity Support
    1 year ago

    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.

  • v-csrikanth's avatar
    v-csrikanth
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
    v-csrikanth
    Icon for Community Support rankCommunity Support
    1 year ago

    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.