Forum Discussion

Olamum's avatar
Olamum
Helper II
2 years ago

Creating a single fiscal date from two dates

Hello Everyone,

A fiscal year in our context spans from April 1st to March 31 of the following year. Typically, when working with data in Power BI, we can view one fiscal year at a time by applying a filter to the page and using advanced filtering to select, for example, fiscal year 2022-2023.

For fiscal year 2022-2023, we set the filter criteria as follows:

  • End date should be on or after 01/04/2022, or it can be blank.
  • Start date should be before 01/04/2023.

This filter setup allows us to focus on data from fiscal year 2022-2023.

  • There may be discrepancies in the data where some end dates are earlier than start dates.
  • Additionally, I'm interested in creating a card that calculates the date difference between the bonus date and the start date, with the goal of obtaining an average of these date differences



link

 

https://docs.google.com/spreadsheets/d/1GFb1iBMEahi0heyJaSuADXisfr0J3Jl5/edit?usp=drive_link&ouid=115136302408500943139&rtpof=true&sd=true

Need Help

4 Replies

    • Olamum's avatar
      Olamum
      Helper II
      2 years ago

      Fiscal year should be declared from April to march of the next year. two dates start and end date should be incompassed into one date so that it can be used as a filter. Can't use the start and end date in the data as a filter to capture many years. 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Hi,

        This approach should work

        1. In Power Query, create one rows for each date which falls between the start and end date
        2. Create a Calendar Table with a relationship (Many to One and Single) from the Date column created ins tep 1 above to the Date column of the Calendar Table
        3. To any visual, drag Date from the Calendar Table

        Hope this helps.