SAMEPERIODLASTYEAR not returning correct values with fiscal year

[bignadad]

My fiscal year is April-March

I have the Fiscal Year setup correctly in my MasterDate table.

I have a Fiscal Year Text that I use for my Slicer and a Fiscal Year Sort to sort properly.

All that is working fine.

This is my measure to get my total amount

Total = CALCULATE(SUM(glentry[amount])*-1)

I have my Slicer filtering for FY23/24 and my total is correct

My LY measure is not correct. Here is the measure.

LY Sales = CALCULATE(glentry[Total], SAMEPERIODLASTYEAR(MasterDate[Dates].[Date]))

Any idea what I'm doing wrong?

[bignadad]

I am pretty new to power bi and not sure how to create the pbix file with sample data. Right now all my data is set up is on dataflows. how can I get them to local sample data?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

How do you know it is not correct?

[DallasBaba]

@lbendlin my assumption about SAMEPERIODLASTYEAR is that the fiscal year starts in April, and traditional time intelligence functions often align with the calendar year.

SAMEPERIODLASTYEAR can be useful in this case if the MasterDate table is correctly set up & marked as a dateTable.

I suggest using DATESYTD or ash coding because there are other ways to achieve the same result.

What are your suggestions? Please advise

[lbendlin]

@DallasBaba SAMEPERIODLASTYEAR is syntax suggar for DATEADD(-1,YEAR), and it works as long as your fiscal years start on the same day.  For other cases like fiscal week driven fiscal years this won't work, and you will need to compute based on the period indicators in the calendar table.

[DallasBaba]

@lbendlin  Thank you for the clarity.

[DallasBaba]

@bignadadThe SAMEPERIODLASTYEAR function may not work as expected because it operates based on calendar years. You can try ash coding:

LY Sales = CALCULATE(
    glentry[Total],
    DATEADD(
        VALUES(MasterDate[Dates].[Date]),
        -1,
        YEAR
    )
)

OR try using the DATESYTD function. 

LY Sales = CALCULATE(glentry[Total], DATESYTD(PARALLELPERIOD(MasterDate[Dates].[Date], 1, "Year")))

This measure will calculate the sales for the previous fiscal year based on the date selected in your slicer. Please note that the DATESYTD function returns the year-to-date total for the given date, so you don’t need to use the TOTALYTD function. 

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note:

If this post is helpful, please mark it as the solution to help others find it easily. Also, if my answers contribute to a solution, show your appreciation by giving it a thumbs up! 

[bignadad]

I tried both but its still not giving the correct data

I labeled the column names with your two suggestions

I have another page where i am manually selecting the dates and the total should be 2,636,714 for LY (April 2022 - March 2023)

[DallasBaba]

@bignadad can you share a pbix file with sample data?