Hi,
I have a relative date slicer that is not working with a SAMEPERIODLASTYEAR-based measure. If I use the normal date slicer between with my DateTable[Date] field, it works fine. My relationships are fine, the date field in my date table is joined to my special dates date field and the data types are the same. The relative date slicer works with every measure that doesn't contain SPLY, so I think it is something to do with the context with the SPLY function.
Here is my code for the SPLY measure in question:
Total Production SPLY =
VAR lastdateAvailable = CALCULATE(MAX(Financials[TransactionDate]), ALL(Financials))
VAR lastyearsameday = lastdateAvailable - 365
VAR ifLY = IF(DAY(lastyearsameday) <> DAY(lastdateAvailable), TRUE(), FALSE())
VAR lastyearsamedayLY = IF(ifLY, lastdateAvailable - 366, lastyearsameday)
VAR SPLYUDate = FILTER(
SAMEPERIODLASTYEAR(DateTable[Date]),
DateTable[Date] <= lastyearsamedayLY
)
RETURN
CALCULATE(SUM(Financials[Amount]), SPLYUDate)
And my relative date field is 'Period' from this table (note: our data updates nightly and does not include today's data, so that's why the dates are like this):
RelativeDates =
VAR _datetable = DateTable
VAR _today = TODAY()
VAR _yesterday = _today - 1
VAR _month = MONTH(_yesterday)
VAR _year = YEAR(_yesterday)
VAR _prevyear = _year - 1
VAR _thismonthstart = DATE(_year, _month, 1)
VAR _thisyearstart = DATE(_year, 1, 1)
VAR _lastmonthstart = EDATE(_thismonthstart, -1)
VAR _lastmonthend = _thismonthstart - 1
VAR _thisquarterstart = DATE(_year, SWITCH(TRUE, _month > 9, 10, _month > 6, 7, _month > 3, 4, 1), 1)
VAR _prevquarterstart = DATE(SWITCH(TRUE, _month < 4, _prevyear, _year), SWITCH(TRUE, _month > 9, 7, _month > 6, 4, _month > 3, 1, 10), 1)
VAR _prevyearstart = DATE(_prevyear, 1, 1)
VAR _startofweek = _yesterday - WEEKDAY(_yesterday - 1)
VAR _startoflastweek = _startofweek - 7
VAR _thismonthend = EDATE(_thismonthstart, 1) - 1
VAR _nextmonthstart = EDATE(_thismonthstart, 1)
VAR _nextmonthend = EDATE(_thismonthstart, 2) - 1
VAR _nextquarterstart = DATE(SWITCH(TRUE, _month > 9, _year + 1, _year), SWITCH(TRUE, _month > 9, 1, _month > 6, 10, _month > 3, 7, 1), 1)
VAR _nextquarterend = EDATE(_nextquarterstart, 3) - 1
VAR _thisyearend = DATE(_year, 12, 31)
VAR _prevYTDstart = DATE(_prevyear, 1, 1)
VAR _prevYTDend = DATE(_prevyear, _month, DAY(_yesterday))
RETURN UNION(
ADDCOLUMNS(FILTER(_datetable, [Date] = _yesterday), "Period", "Yesterday", "Order", 1),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _startofweek && [Date] < _yesterday), "Period", "This Week", "Order", 2),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _startoflastweek && [Date] < _startofweek), "Period", "Previous Week", "Order", 3),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _yesterday - 6 && [Date] < _yesterday), "Period", "Last 7 Days", "Order", 4),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _thismonthstart && [Date] <= _thismonthend), "Period", "This Month", "Order", 5),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _lastmonthstart && [Date] <= _lastmonthend), "Period", "Previous Month", "Order", 6),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _prevyearstart && [Date] < _thisyearstart), "Period", "Previous Year", "Order", 7),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _thisyearstart && [Date] < _yesterday), "Period", "Year To Date", "Order", 8),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _prevYTDstart && [Date] < _prevYTDend), "Period", "Previous YTD", "Order", 9),
ADDCOLUMNS(_datetable, "Period", "Custom...", "Order", 10),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _thisquarterstart && [Date] < _yesterday), "Period", "Quarter To Date", "Order", 11),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _prevquarterstart && [Date] < _thisquarterstart), "Period", "Previous Quarter", "Order", 12),
ADDCOLUMNS(FILTER(_datetable, [Date] > _yesterday), "Period", "Future...", "Order", 13),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _nextmonthstart && [Date] <= _nextmonthend), "Period", "Next Month", "Order", 14),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _thisquarterstart && [Date] <= _thisyearend), "Period", "This Quarter", "Order", 15),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _nextquarterstart && [Date] <= _nextquarterend), "Period", "Next Quarter", "Order", 16)
)
I'm sure it's something so simple I'm overlooking but it's driving me mad.
Thank you!
