SAMEPERIODLASTYEAR vs time slicer issue - it reflects full month last year, not to date in slicer

[PowerBI_Lamer]

I am experiencing unpleasent behaviour of time slicer.

1) I have sales table day by day (lets say with no record for sundays) from 2018-01-01 to yesterday.

2) Calendar connected to it is made like:

CALENDAR (DATE(2018;1;1); TODAY());

3) Time slicer is based on callendar.

4) Measure showing previous year is like:

CALCULATE(

SUM(salestable[Value]);

SAMEPERIODLASTYEAR(calendar[Date])

)

5) By default time slicer 'to' date is last day from calendar.

So now we have 2020-02-14, the last date in sales table is 2020-02-13, 'from' date in time slicer is 2020-01-01.

When 'to' date in time slicer is set to 2020-02-13 charts show actual sales compared to 2019-01-01 -> 2019-02-13, which is fine.

But,

when 'to' date in time slicer is set to 2020-02-14 (which is default) charts show actual sales compared to 2019-01-01 -> 2019-02-28.

Can anyone help me to understand why, and how to override/overcome this?

P.S.

The same kind of issue happens when I filter date slicer in 'from' date.

Lets say I filter from 2019-01-01, when you compare data from 2019-01-02 everything is fine, but reach begin of a slicer - in this case 2019-01-01 measure summs everything what were there before.

[Anonymous]

I have the exact same problem. @PowerBI_Lamer Did you find  a solution?

[PowerBI_Lamer]

No solution for that, as this is minor issue for Microsoft 😉

[dax]

Hi @PowerBI_Lamer ,

As I know, SAMEPERIODLASTYEA returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. So when you choose 2019/1/1-2020/2/3, it will return the value from 2019/1/1-2019/2/3. 

In addition, you siad that when 'to' date in time slicer is set to 2020-02-14 (which is default) charts show actual sales compared to 2019-01-01 -> 2019-02-28, so did you mean that you still have result of 2019/2/14-2019/2/28 in chart? You could try to show this in table to see whether it have this part result in it.

In addition, you also could upload your sample file which will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBI_Lamer]

I don't know how to upload a file (60KB). But you can make it yourself in seconds. Just make a table that contains column with dates from 2018-01-01 to now, and another column that contains same value for each day. Callendar as I mentioned in first entry here, and one simple relation between dates in those tables. Everything is visible on picture above. Values are green and Previous Year Values ar as target value on the chart.

[dax]

Hi @PowerBI_Lamer , 

I test this in my environment, I find I also have the same experience, I think you could try to post this in Issues or power-bi-ideas . 

In addition, you also could refer tomy sample for workaround.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBI_Lamer]

I don't feel comfortable with this workarround.

Now it doesn't really work as on a date slicer max date is max from callendar table, not data table, effecively today you compare  (2019-01-01 to 2019-02-20) with (2020-01-01 to 2020-02-13).

I know I could make it work but this is really simple example and I'm afraid to get lost when the tables aren't related with callendar and the structure is getting more complicated.

Your workarround gave me an idea how to secure it from the top: First search in several data tables for max date, and then make it last date in callendar table, but unfortunately with SAMEPERIODLASTYEAR it doesn'd work either.

Anyway, I'll do as you suggested, and post it to Issues.

[amitchandak]

Just make sure the calendar is having date 14-feb.

Try datesmtd or totalmtd

Example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,year)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[PowerBI_Lamer]

---

@amitchandak wrote:

Just make sure the calendar is having date 14-feb.

Try datesmtd or totalmtd

---

Yes it has. The last day is Today, that is 2020-02-14 Valentine's day.

MTDs don't help as I need to use it not always from the first day of the month.