topic Re: Sameperiodlastyear funcion issue in DAX Commands and Tips

Sameperiodlastyear funcion issue

Adidas — Tue, 02 Jul 2019 05:38:06 GMT

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?

Re: Sameperiodlastyear funcion issue

Anonymous — Tue, 02 Jul 2019 09:16:26 GMT

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

Re: Sameperiodlastyear funcion issue

Adidas — Tue, 02 Jul 2019 13:36:10 GMT

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

Re: Sameperiodlastyear funcion issue

Anonymous — Tue, 02 Jul 2019 16:14:00 GMT

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.

Re: Sameperiodlastyear funcion issue

Adidas — Tue, 02 Jul 2019 19:37:54 GMT

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

Re: Sameperiodlastyear funcion issue

Anonymous — Wed, 03 Jul 2019 07:56:14 GMT

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