Forum Discussion
SAMEPERIODLASTYEAR WITH TREATAS
Hello,
I do not understand why SAMEPERIODLASTYEAR does not filter. Could you please help me.
Net Sales Value (same period last year) =
CALCULATE (
sum('Sales'[NetSalesValue])
,REMOVEFILTERS('Company'[Name])
,TREATAS (VALUES('Company'[Name]),'Sales'[Company]))
-- USERELATIONSHIP('Calendar'[Date],'Sales'[Date])
,REMOVEFILTERS ('Calendar'[Date])
,TREATAS (VALUES('Calendar'[Date]),'Sales'[Date])
,SAMEPERIODLASTYEAR ('Calendar'[Date])
)
There is a page slicer with 'Calendar'[Date] field. This slicer creates a filter context for a measure.
There is an active relationship between 'Calendar' and 'Sales' tables. Which I do not need, that is why I wrote REMOVEFILTERS ('Calendar'[Date])
When I use an inactive relationship between 'Calendar' and 'Sales' tables, and do not use REMOVEFILTERS and TREATAS functions, the measure evaluates correctly. But I when use REMOVEFILTERS and TREATAS functions instead of USERELATIONSHIP, is shows blank values. There is no error, just blank values in the table visual.
I had to delete an inactive relationship in the data model, because it breaks other reports.
Whereas the following measure evaluates correctly without errors
Net Sales Value =
CALCULATE (
sum('Sales'[NetSalesValue])
,REMOVEFILTERS('Company'[Name])
,TREATAS (VALUES('Company'[Name]),'Sales'[Company]))
)- Anonymous2 years ago
Hi OlegV ,
Based on the description, SAMEPERIODLASTYEAR DAX have some limitations.
Try to verify that the table contains date column mark as Date Table. The inactive relationship is not suitable for SAMEPERIODLASTYEAR.
Besides, try to use ALL DAX to ignore any filters.
ALL function (DAX) - DAX | Microsoft Learn
You can view the following documents to learn more information.
SAMEPERIODLASTYEAR function (DAX) - DAX | Microsoft Learn
Solved: SAMEPERIODLASTYEAR with a year filter - Microsoft Fabric Community
Solved: SAMEPERIODLASTYEAR with date filter - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlinSuper User
Why are you removing the dimension filters? How does your data model look like?
- AnonymousNot applicable
Hi OlegV ,
Based on the description, SAMEPERIODLASTYEAR DAX have some limitations.
Try to verify that the table contains date column mark as Date Table. The inactive relationship is not suitable for SAMEPERIODLASTYEAR.
Besides, try to use ALL DAX to ignore any filters.
ALL function (DAX) - DAX | Microsoft Learn
You can view the following documents to learn more information.
SAMEPERIODLASTYEAR function (DAX) - DAX | Microsoft Learn
Solved: SAMEPERIODLASTYEAR with a year filter - Microsoft Fabric Community
Solved: SAMEPERIODLASTYEAR with date filter - Microsoft Fabric Community
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.