Forum Discussion
SAMEPERIODLASTYEAR return a wrong result when the date is the last in the calendar
Hi Everyone, I hope you are doing well. I have a simple star schema model (a calendar dimension and a fact sales table). When I tried to calculate the MTD value last year with SAMEPERIODLASTYEAR, ...
Anonymous this might work
_MTD_TC_PY = VAR _END = MAX(DIM_CALENDAR[DATE]) VAR _START = DATE(YEAR(_END), MONTH(_END), 1) RETURN CALCULATE([_TC], SAMEPERIODLASTYEAR(DATESMTD(DIM_CALENDAR[DATE])),DIM_CALENDAR[DATE]<=EDATE(_END,-12))
wdx223_Daniel
Community Champion
Community ChampionAnonymous this might work
_MTD_TC_PY =
VAR _END = MAX(DIM_CALENDAR[DATE])
VAR _START = DATE(YEAR(_END), MONTH(_END), 1)
RETURN CALCULATE([_TC], SAMEPERIODLASTYEAR(DATESMTD(DIM_CALENDAR[DATE])),DIM_CALENDAR[DATE]<=EDATE(_END,-12))Hi wdxeukgsq13
Thank you for your reply.
Yes. It can be an ad-hoc solution. It's the same as the following method:[_TC_PY] = VAR _END = EDATE(MAX(DIM_CALENDAR[DATE]),-12) RETURN CALCULATE([_TC], SAMEPERIODLASTYEAR(DIM_CALENDAR[DATE]), DIM_CALENDAR[DATE] <= _END)But it means we have to filter the 'SAMEPERIODLASTYEAR' manually for it returns more dates than we expect. Do you have any idea about why it returns more dates than we need?
- wdx223_Daniel
Anonymous TimeIntellgent Functions are based on standard date table. might your date table's last day is 2020-11-10, so these function consider date from 2020-11-1 to 2020-11-10 is a whole month, so they return a whole month of last year. this is my guess.
wdx223_Daniel
Yes, it did. I agree with your guess.
I wonder, is it normal and in-design? or it's a bug in timeintelligent DAX?