Previous Fiscal Year Data by Our own Calendar that may differs every Year on Year and Month on Month

[DharaniPrathap]

Hello Community,

Need a help on Previous Fiscal Year Data by Our own Calendar that may differs every Year on Year and Month on Month.

Explination :

We have our own Fiscal calender based on weekly data as below,

Sep-2025 :  31 Aug-24 to 27 Sep-24

Sep-2024 :  02 Sep-23 to 29 Sep-23

Sep-2023 :  03 Sep-22 to 30 Sep-22

Sep-2022 :  04 Sep-21 to 01 Oct-21

My Current Year Data is coming correct as per this dates when I Select 2025 Fiscal Year Slicer Selection Sep-2025 :  31 Aug-24 to 27 Sep-24.

I want Previous Year data while I select 2025 Fiscal Year Slicer my Previous Year Line will be Sep-2024 :  02 Sep-23 to 29 Sep-23 this period, I tried with SameperiodLastYear, Dateadd, DatesInPeriod, DatesBetween all are getting wrong please help me here to resolve the mentioned issue. Any more clarification please ping me.

Thanks In Advance.

[v-karpurapud]

Hi @DharaniPrathap 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @DharaniPrathap 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @DharaniPrathap 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @DharaniPrathap 

Welcome to the Microsoft Fabric Forum,

Thank you @bhanu_gautam  for the contribution.

Regarding the creation of a measure to handle Previous Year using Custom Fiscal Logic:

1. Create a custom measure by mapping each fiscal period to its corresponding period in the previous fiscal year            using a lookup approach.

DAX

Measure_PreviousYear = 
VAR CurrentMonth = SELECTEDVALUE(FiscalCalendar[FiscalMonthYear])
VAR PrevMonth = 
    LOOKUPVALUE(
        MappingTable[PrevFiscalMonthYear],
        MappingTable[FiscalMonthYear], CurrentMonth
    )
RETURN
CALCULATE(
    SUM(FactTable[Amount]),
    FILTER(
        ALL(FiscalCalendar),
        FiscalCalendar[FiscalMonthYear] = PrevMonth
    )
)

2. To dynamically filter your visuals to just the last 36 fiscal months, add a Calculated Column in your FiscalCalendar.

DAX

IsLast36Months = 
VAR MaxFiscalDate = CALCULATE(MAX(FiscalCalendar[FiscalStartDate]), ALL(FiscalCalendar))
VAR ThisMonthStart = FiscalCalendar[FiscalStartDate]
RETURN
DATEDIFF(ThisMonthStart, MaxFiscalDate, MONTH) <= 36

 3. To limit charts to only show data from the last 36 custom fiscal months:

DAX

Measure_Last36Months =
CALCULATE(
    [YourMeasure],
    FILTER(
        FiscalCalendar,
        FiscalCalendar[IsLast36Months] = TRUE
    )
)

If this information helps resolve your issue, kindly consider marking this response as the Accepted Solution, as it may assist other community members facing similar challenges.

Thank you for being part of the Microsoft Fabric Community.

[DharaniPrathap]

Hello bhanu_gautam,

Thanks for your quick replay, 
need more clarity I need Dynamic last 36 months entire calender.
How do i do that for all last 36 month that is Mar'25 to Mar'22 and it's dynamically change the date for every month.

Thanks In Advance

[bhanu_gautam]

@DharaniPrathap 

You can create a custom date table in Power BI using DAX. Here is an example:

CustomDateTable =
ADDCOLUMNS (
CALENDAR (DATE(2021, 8, 31), DATE(2025, 9, 27)),
"FiscalYear",
SWITCH (
TRUE(),
[Date] >= DATE(2024, 8, 31) && [Date] <= DATE(2024, 9, 27), "2025",
[Date] >= DATE(2023, 9, 2) && [Date] <= DATE(2023, 9, 29), "2024",
[Date] >= DATE(2022, 9, 3) && [Date] <= DATE(2022, 9, 30), "2023",
[Date] >= DATE(2021, 9, 4) && [Date] <= DATE(2021, 10, 1), "2022",
BLANK()
),
"FiscalMonth",
FORMAT([Date], "MMM")
)

Ensure that your data model uses this custom date table for date-related calculations. You can link it to your fact table based on the date column.

Now, you can create a measure to calculate the previous fiscal year data. 

PreviousFiscalYearData =
CALCULATE (
SUM ( 'YourFactTable'[YourMeasure] ),
FILTER (
ALL ( 'CustomDateTable' ),
'CustomDateTable'[FiscalYear] =
VALUE ( SELECTEDVALUE ( 'CustomDateTable'[FiscalYear] ) ) - 1
)
)