topic Re: LY Fiscal Calculation in DAX Commands and Tips

LY Fiscal Calculation

Brendy_P — Wed, 19 Nov 2025 12:00:50 GMT

Hi All,

Hopefully I can explain my dilemma adequately for you to help me. Thank you in advance.

I am looking to calculate last years total from my Calendar table which is based on my company's fiscal year.

The problem is that the Dax calculation using DateAdd -1 year or Sameperiodlastyear doesn't give me the correct total.

example - Calculate([Total],SAMEPERIODLASTYEAR(Calendar[Date])

The third Figure below is a snippet from my Visualisation. The filter Context comes from the Slicer (Period Yrs). And also from the Table (Company Weeks)

I want to create a measure that takes the period yr 25/26, go back to 24/25 and start calculating the total from company weeks 1.

Instead what I am currently getting is the total based on the dates starting on the 31/3/24 to 6/4/24. Were it should be 1/4/24 to 7/4/24 (see second table)

If you need me to explain anything else please let me know.

Re: LY Fiscal Calculation

amitchandak — Wed, 19 Nov 2025 12:13:07 GMT

Brendy_P , We got a calendar option in Sep 2025
https://powerbi.microsoft.com/en-in/blog/power-bi-september-2025-feature-summary/#post-30998-_Toc208591563

Re: LY Fiscal Calculation

MohdZaid_ — Wed, 19 Nov 2025 12:33:11 GMT

Hey Brendy_P ,

The issue is not with SAMEPERIODLASTYEAR it's that your fiscal calendar does not align with the true date shift that DAX performs. DAX always offsets by 365 days, not by your Company Week 1 --> Week 1 fiscal structure.

To get the correct result, you must shift fiscal periods, not dates.

The fix is to use a Fiscal Year + Fiscal Week mapping and manually jump to the pervious fiscal year while forcing Week = 1.

Measure (Fiscal LY Todal)

LY Total =
VAR CurrFY =
SELECTEDVALUE ( Calendar[FiscalYear] )
VAR PrevFY =
CurrFY - 1
RETURN
CALCULATE (
[Total], -- your base measure
Calendar[FiscalYear] = PrevFY,
Calendar[FiscalWeek] >= 1 -- ensures you start at Company Week 1
)

It ignores the natural calendar date offset.
It explicity tells Power BI : "Go to Fiscal Year (FY - 1) and start calculating from Week 1"
This aligns perfectly with your compnay's 1/4-31/3 fiscal structure.

I ran into the exact same mismatch before DAX was technically "correct" but not fiscally correct. Shifting by fiscal attributes instead of dates is the key.

If it solved your issue, feel free to mark it as the solution so others can benefit too.

Thanks for being part of the community.

Re: LY Fiscal Calculation

Brendy_P — Wed, 19 Nov 2025 14:11:09 GMT

Hi amitchandak

I have looked at the blog and have already got the enhanced calendar option. I have followed the instructions but I can't seem to find the calendar created. I will need to do a bit more reding on it. Thank you for replying

Re: LY Fiscal Calculation

Brendy_P — Wed, 19 Nov 2025 14:28:13 GMT

Hi MohdZaid_

Thank you for your reply.

I have tried the measure but I get an error. Because SELECTEDVALUE ( Calendar[FiscalYear] ) is formatted as a text value in my calendar table, you can't minus 1 from it. i.e. 25-26. I feel that you are close to the solution or maybe it is something that I am doing wrong. Or need to adjust on my calendar table.

Re: LY Fiscal Calculation

Praful_Potphode — Thu, 20 Nov 2025 06:57:35 GMT

Hi Brendy_P ,

Can you try TOTALYTD DAX Function for current year and previous year,it has one additional parameter to switch year end.

try measures below:

Total = SUM(TD[SalesAmount])

Current year = TOTALYTD( [Total] ,'Calendar'[Date] ,"03/31" //here my year end is 31st march change it as per your company )

Previous Year = TOTALYTD( [Total], SAMEPERIODLASTYEAR('Calendar'[Date]) ,"03/31" )

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Re: LY Fiscal Calculation

MohdZaid_ — Thu, 20 Nov 2025 07:38:57 GMT

Hey Brendy_P ,

The fix is simple create a numeric Fiscal year first, then build the text labels from that.

DAX for Year from date and month -

DAX for FY from Year -

A reliable pattern you can reuse anythime a client needs fiscal year slicing.

Re: LY Fiscal Calculation

Brendy_P — Thu, 20 Nov 2025 09:09:17 GMT

Hi Praful_Potphode

Thank you for your reply, your solution is 99.9% correct.

The correct cumlative total should be 130.

As you can see from below, Company week's should include the date 7th April for last year.

Re: LY Fiscal Calculation

Brendy_P — Thu, 20 Nov 2025 09:35:01 GMT

Hi MohdZaid_

I will try what you have suggested and get back to you. Thanks

Re: LY Fiscal Calculation

Praful_Potphode — Thu, 20 Nov 2025 10:41:13 GMT

Hi Brendy_P

In my solution, "03/31" is the year end which translates to 31st March

Can you put youryear end in formula and try again.

Please give kudos or mark it as solution once confirmed

Thanks and regards,

Praful

Re: LY Fiscal Calculation

amitchandak — Thu, 20 Nov 2025 11:34:39 GMT

Brendy_P , If you have Oct-2025 or onward

New Calendar

Do these entries

Formula

YTD FY = CALCULATE([Net], DATESYTD('FY'))

Re: LY Fiscal Calculation

Brendy_P — Tue, 25 Nov 2025 07:11:51 GMT

amitchandak Praful_Potphode MohdZaid_

I appreciate and would like to thank you all for your help. I have since found out that my company uses a 4-4-5 week calendar. Having said that amitchandak suggestion probably answers my qustion. Thank you all