Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Everyday 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
Solved! Go to Solution.
@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 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?
@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?
@camargos88 @Anonymous .This will work for you .
Instead of All table .use filter table .
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 Hey Mate,
if you could use this code as a new table ,
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 can you show me the output.
and Kindly change cross filtering direction from single to bi directional .
and tell me the output.
Thank you
@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.
@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))
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.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |