SAMEPERIODLASTYEAR for Fiscal year

[imranami]

Hello. I have a Date table with a one to many join to a date field in a sales table. The Date table has a column which identifies the correct financial year for each date (FY18, FY19, FY20). The financial year is 31st  March to the 1st April.

Depending on the financial year selected from a slicer, I need a visual to display sales data for the selected financial year as well as the previous financial year (if a user selects FY19, they should also see data for FY18).

I was previously SAMEPERIODLASTYEAR using until I realised this measure was calculating by calendar year rather than fiscal year.

Total Value Previous Year =

CALCULATE(

    [Total Sales] ,

        SAMEPERIODLASTYEAR( Dates[Date]))

This is a common problem within the community forums but I cannot seem to identify the correct solution for my scenario. Any support would be appreciated.

regards,

ImranAmi

[imranami · Accepted solution]

Looks the guidance provided in this article has resolved the issue using the below article (Russo & Ferrari).

https://www.daxpatterns.com/time-patterns/

Added an additional date column to my table which subtracts the current date by one year:

PY Date = DATE(YEAR(Dates[Date])-1,MONTH(Dates[Date]),DAY(Dates[Date]))

Then used the following DAX to return the sales amount from the previous year:

Total Value Previous Year =

CALCULATE (

[Total Sales],

FILTER (

ALL ( Dates ),

Dates[Year] = MAX ( Dates[Year])-1

&& Dates[Date] <= MAX ( Dates[PY Date] )

))

[johnny4]

Great solution, thank you!