Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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, I got a weird/wrong result below:

D3ZfkWlEveryday works fine, but the latest one (2020-11-10) in the calendar returns the whole month's value last year instead of 10.
If I insert a new date (e.g., 2020-11-11) into the calendar table, then the 2020-11-10 will work fine, and the new one will work fail like the previous one.

 

MEASURES:

 

 

 

[_TC] = COUNTROWS('FACT_SALES')
[_TC_PY] = CALCULATE([_TC], ALL('DIM_CALENDAR'), SAMEPERIODLASTYEAR('DIM_CALENDAR'[DATE])) 
[_MTD_TC_PY] =
VAR _END = LASTDATE(VALUES('DIM_CALENDAR'[DATE]))
VAR _START = DATE(YEAR(_END), MONTH(_END), 1)
RETURN CALCULATE([_TC_PY], FILTER(ALL('DIM_CALENDAR'), 'DIM_CALENDAR'[DATE] >= _START && 'DIM_CALENDAR'[DATE] <= _END)) 

 

 

I attached the sample file here: MTD_DATE_PY_ERROR.pbix
Does anyone have any clue about this?

I doubt it's a bug of date DAX function. And it can also be found in the Tabular Model of Analysis Service.

Thanks,
Gray

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

12 REPLIES 12
wdx223_Daniel
Super User
Super User

@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))
Anonymous
Not applicable

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?

@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.

Anonymous
Not applicable

@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?

Anonymous
Not applicable

@camargos88 @Anonymous  .This will work for you .
Instead of All table .use filter table .

_TC_PY_SAM =
CALCULATE([_TC],FILTER(DIM_CALENDAR,SAMEPERIODLASTYEAR(DIM_CALENDAR[DATE])))
then use this one 

_MTD_TC_PY sam =
VAR _END = LASTDATE(VALUES(DIM_CALENDAR[DATE]))
VAR _START = DATE(YEAR(_END), MONTH(_END),1)
RETURN CALCULATE([_TC_PY_SAM], FILTER(ALL('DIM_CALENDAR'), 'DIM_CALENDAR'[DATE] >= _START && 'DIM_CALENDAR'[DATE] <= _END))


Help date.png
 
if is it as per your requirement .Kindly mark this as a solution  and give kudos as well.
Anonymous
Not applicable

Hi @Anonymous 
Sorry about that I found your method still returns the MTD for the current year.

But do you know why the function ALL('DIM_CALENDAR') makes the latest date perform like that?

Anonymous
Not applicable

@Anonymous  Hey Mate,
if you could use this code as a new table ,

test = FILTER(DIM_CALENDAR,SAMEPERIODLASTYEAR(DIM_CALENDAR[DATE])) .
you will understand what is doing in backend .

ALL('DIM_CALENDAR') is ignoring date context .it is calculating for all calender .

Thank you .
Anonymous
Not applicable

Hi @Anonymous ,
Sorry about that I found your method still returns the MTD for the current year...
So this issue will be opened again. I appreciate your time.

Anonymous
Not applicable

@Anonymous  can you show me the output.

and Kindly change cross filtering direction from single to bi directional .

and tell me the output.

help.JPG
Thank you 

Anonymous
Not applicable

@Anonymous 

I've changed the relationship between DIM_CALENDAR and FACT_SALES to bi-directional.

And I set the value in past year to 1 and set the value in current year to 2
You can see that both the _TC_PY and the _MTD_TC_PY are calculating the value for current year with your method.

gray0012_1-1605248248534.png

 

camargos88
Community Champion
Community Champion

@Anonymous ,

 

Try this measure:

_MTD_TC_PY = 
VAR _END = LASTDATE(VALUES(DIM_CALENDAR[DATE]))
VAR _START = DATE(YEAR(_END), MONTH(_END), 1)
RETURN CALCULATE([_TC], FILTER(ALL('DIM_CALENDAR'), 'DIM_CALENDAR'[DATE] >= _START && 'DIM_CALENDAR'[DATE] <= _END))


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Hi @camargos88 
Thank you for the reply. And I'm really appreciate your time.

The measure you provided will return an MTD value for the current year.
But what I need is MTD value for the last year.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors