LY Fiscal Calculation

[Brendy_P]

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.

[Praful_Potphode]

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

[Brendy_P]

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.

[Praful_Potphode]

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

[MohdZaid_]

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.

[Brendy_P]

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. 

[MohdZaid_]

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.

[Brendy_P]

Hi @MohdZaid_ 

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

[amitchandak]

@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-_Toc208...

[Brendy_P]

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

[amitchandak · Accepted solution]

@Brendy_P , If you have Oct-2025 or onward 

New Calendar 

Do these entries 

Formula 

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

[Brendy_P]

Hi

@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