Forum Discussion

Adidas's avatar
Adidas
Icon for Helper I rankHelper I
7 years ago

Sameperiodlastyear funcion issue

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?

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Adidas
      Icon for Helper I rankHelper I
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.