Need Help With Fiscal Year Comparison and Creating Correct Measures

[nunezjo]

Hello Power BI Community,

I am currently working on a project involving fiscal year (FY) data, and I am struggling to correctly compare data between different FYs due to variations in their start and end dates.

To clarify, in our organization, FY does not align with the calendar year. For instance, FY 2022 spans from January 30, 2022, to January 28, 2023, whereas FY 2023 is from January 29, 2023, to February 3, 2024.

In my report, I am supposed to provide measures for "This Year" and "YTD". For "This Year", the data range depends on the scope we are looking at (for example, it could be March 1 to October 1). On the other hand, "YTD" represents data from the start of the current FY to the current date.

My challenge comes in when I try to compare the data of "This Year" with "Last Year". Given the discrepancy in FY dates, how do we align "This Year" and "Last Year" for an apples-to-apples comparison?

Another issue arises when I try to fetch data for "Last Year". I am using the following formula:

Gross Script Ct LY = 
CALCULATE (
    'LDRSHP_RPT_FAC'[Gross Script Count],
    FILTER (
        ALL (DT_DIM),
        CONTAINS (
            VALUES (DT_DIM[DAY_DT_LY]),
            DT_DIM[DAY_DT_LY], DT_DIM[DAY_DT]
        )
    )
)

The filter appears to be pulling data from 3/25/2023 - 2/3/2024, which aligns with this year's dates, while it should reflect last year's dates. The data values themselves are correct, implying the data range is technically 3/25/2022 - 2/3/2023, but this goes past FY 2022.

I do have a "Last Year" field in my DT_DIM table, where I've marked the rows pertaining to FY 2022. When I use this field as a filter, it fetches the right data for the correct FY. However, this limits my capabilities to manipulate and use the data further in my reports.

Ultimately, my primary question is: how can I construct an accurate comparison between "This Year" and "Last Year" given the variation in fiscal year dates? How do I align the data correctly for a fair comparison, especially when the current year has more days than the last? Additionally, any suggestions for correcting my "Gross Script Ct LY" measure would be greatly appreciated.

Thank you for your time and assistance.

In using the different LY methods one using the Gross Script Ct LY measure and the other using the LY date dimension field as a filter to the visual, there is a huge difference in the script count, by millions so it's important to figure out what version I should use to show Last Year and This Year especially if we are comparing between the 2. 

DT Dim below.