Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Hi @premod ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@premod , The Same period last year should be used in measures like
if(Max(DateTable[Date]) <= lastyearsamedayLY, CALCULATE(SUM(Financials[Amount]), SAMEPERIODLASTYEAR(DateTable[Date])))
If you need custom period time intelligence refer
Power BI Custom Period Till Date (PTD)- https://youtu.be/rQ3Z_LtxwQM
1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
Power BI Date Table: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=18180s
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |