Forum Discussion
Helper IVDAX Last Year based on custom date dimension
Hi sabd80,
Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.
I am also including .pbix file for your better understanding, please have a look into it:
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Fabric Community Forum.
Super Userhi
When you have a custom date table, standard Time Intelligence functions like SAMEPERIODLASTYEAR() don’t work unless the table is marked as a Date Table and connected properly. Since you’re on a live connection, you also can’t add calculated columns easily.
You can still calculate Last Year measures using DAX with FILTER and MAX on the date table.
✅ Example: Last Year Sales
Explanation:
SelectedYear captures the year currently selected in your slicer.
ALL('Date') removes filters on the Date table.
FILTER picks the rows from the previous year.
[Total Sales] is your existing measure.
✅ Dynamic by Dimension
Because the filter is only applied to the Date table, the measure still respects other dimensions like Customer or Product:
- Customer A → Last Year Sales shows filtered for Customer A - Product X → Last Year Sales shows filtered for Product X
Helper IVNabha-Ahmed thank you so much for your reply.
Your solution works very well with Fiscal Year and other dimensions, but when I add month and week to the visual the number is repeated, it does not break it down by those date attributibutes.
- Nabha-Ahmed
Hi again
"The issue was caused because the previous measure filtered only by FinancialYear, so Month and Week could not break down the values. I fixed it by using a date-range–based calculation for the entire previous year, which allows Month and Week to aggregate correctly. Now the measure breaks down properly at all date levels
- Nabha-Ahmed
Try this code
Dax measure:
LastYearSales :=
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR LastYearStart =
DATE ( YEAR ( MaxDate ) - 1, 1, 1 )
VAR LastYearEnd =
DATE ( YEAR ( MaxDate ) - 1, 12, 31 )
RETURN
CALCULATE (
[Total Sales],
FILTER (
ALL ( 'Date' ),
'Date'[Date] >= LastYearStart
&& 'Date'[Date] <= LastYearEnd
)
)- sabd80
Nabha-Ahmed the above code did not work.
I have replaced the code of LastYearStart and LastYearEnd to point to the fiscal year start date and fiscal year end date, but it did not work- sabd80
Nabha-Ahmed unfortunitally it does not work, it repeats the yearly figure on month and week level.
This my DAX:Testing2 Measture LY =VAR currentYear= MAX ( 'Date'[Fiscal Year] )VAR LastYearStart = CALCULATE(MAX('Date'[Fiscal Year Start Date]), FILTER(ALL('Date'[Fiscal Year]),'Date'[Fiscal Year] = currentYear-1))VAR LastYearEnd = CALCULATE(MAX('Date'[Fiscal Year End Date]), FILTER(ALL('Date'),'Date'[Fiscal Year] = currentYear-1))RETURNCALCULATE ([Measture],FILTER (ALL('Date') ,'Date'[Calendar Date] >= LastYearStart &&'Date'[Calendar Date] <= LastYearEnd))