topic Re: Previous Fiscal Year Data by Our own Calendar that may differs every Year on Year and Month on Month in DAX Commands and Tips

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

DharaniPrathap — Mon, 17 Mar 2025 08:34:09 GMT

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.

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

bhanu_gautam — Mon, 17 Mar 2025 09:15:36 GMT

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
)
)

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

DharaniPrathap — Mon, 17 Mar 2025 10:44:25 GMT

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

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

v-karpurapud — Tue, 13 May 2025 08:55:51 GMT

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.

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

v-karpurapud — Fri, 16 May 2025 12:40:13 GMT

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.

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

v-karpurapud — Wed, 21 May 2025 12:05:33 GMT

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.

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

v-karpurapud — Thu, 29 May 2025 12:43:04 GMT

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.