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...
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.

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

        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