Forum Discussion
SAMEPERIODLASTYEAR for Fiscal year
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.
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
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] )))
2 Replies
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] )))Great solution, thank you!
