Forum Discussion

Brendy_P's avatar
Brendy_P
Icon for Helper I rankHelper I
9 months ago
Solved

LY Fiscal Calculation

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.

 

 

 

 

 

 

 

  • amitchandak's avatar
    amitchandak
    9 months ago

    Brendy_P , If you have Oct-2025 or onward 

    New Calendar 

    Do these entries 

    Formula 

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

11 Replies

    • Brendy_P's avatar
      Brendy_P
      Icon for Helper I rankHelper I
      9 months ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        9 months ago

        Brendy_P , If you have Oct-2025 or onward 

        New Calendar 

        Do these entries 

        Formula 

        YTD FY = CALCULATE([Net], DATESYTD('FY'))
  • MohdZaid_'s avatar
    MohdZaid_
    Icon for Super User rankSuper User
    9 months ago

    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's avatar
      Brendy_P
      Icon for Helper I rankHelper I
      9 months ago

      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_'s avatar
        MohdZaid_
        Icon for Super User rankSuper User
        9 months ago

        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.



  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    9 months ago

    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's avatar
      Brendy_P
      Icon for Helper I rankHelper I
      9 months ago

      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's avatar
        Praful_Potphode
        Icon for Super User rankSuper User
        9 months ago

        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