Sameperiodlastyear funcion issue

[Adidas]

Hello folks,

I'm struggling with this one and couldn't find my way out

My goal is to show a Bar chart with current year months on X-axis, and monthly Sales of current/last year on values.

for that I created a measure consists of the SAMEPERIODLASTYEAR function (for last year values)

everything works fine but with one little bug:

On the 1st day of every month, the SAMEPERIODLASTYEAR function returns the sales for a single day (the 1st of same month last year) instead of the entire month

from the second of the month and further, there is no problem.

for example:

suppose last year daily sales equal to 100

here the results on 1/7/2019

here the results on 2/7/2019 and further:

any ideas how to solve this?

[Anonymous]

Hi.

First of all, do you have a proper date table in your model that is designated as such and covers all FULL years for your date columns in any of your tables? If you don't, then time-BI functions will not work correctly. To me it looks like you're using time-BI functions without a proper date table. Might be wrong, of course.

Best

Darek

[Adidas]

Hi Darek

I do have a time dimension table generated by this Dax code:

Date =
VAR BASECALENDAR =
    CALENDAR ( DATE ( 2009, 01, 01 ), TODAY () )
RETURN
    GENERATE (
        BASECALENDAR,
        VAR BASEDATE = [DATE]
        VAR YEARDATE =
            YEAR ( BASEDATE )
        VAR MONTHNUMBER =
            MONTH ( BASEDATE )
        VAR MONTHNAME =
            FORMAT ( BASEDATE, "MMMM" )
        VAR YEARMONTHNAME =
            FORMAT ( BASEDATE, "MMM-YY" )
        VAR YEARMONTHNUMBER = ( YEARDATE * 12 + MONTHNUMBER ) - 1
        RETURN
            ROW (
                "DAY", BASEDATE,
                "YEAR", YEARDATE,
                "MONTH NUMBER", MONTHNUMBER,
                "MONTH", MONTHNAME,
                "YEAR MONTH NUMBER", YEARMONTHNUMBER,
                "YEAR MONTH", YEARMONTHNAME
            )
    )

the sales fact table is linked to this dimension by inv date = dim date

---

---

[Anonymous]

Why would you have cross-filter enabled on the relationship? What's the use of it? You should switch to one-way filtering from dim to the fact table and then see what happens.

[Adidas]

Hi

I've built a demo model to isolate the problem,

it's one to one relationship so I'm forced to set both ways join

however, it has nothing to do with my issue since in the original model it's one-way join and still the same wrong calculation.

I'm starting to believe that Sameperiodlastyear function is context related and was design to work like this

[Anonymous]

Hi there.

Here's the documentation of the function: https://dax.guide/sameperiodlastyear/ as I don't know how I could be of any more help...

Sorry.

Best

Darek