Forum Discussion

OlegV's avatar
OlegV
Helper III
2 years ago
Solved

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]))
)





2 Replies