Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

SAMEPERIODLASTYEAR over multiple years

Hi all,

 

I have been doing some reading on SAMEPERIODLASTYEAR and am obviously missing something important.

 

I am trying to achieve the following:

 

  • Preferred option: add to the graph below a second bar (maybe on secondardy axis) that would show the same amounts for the current period in the previous year (ie 2015 would be based on 1-Jan-2015 to 6-Jan-2015, 2016 would be 1-Jan-2016 to 6-Jan-2016, etc). 

     

 

 

 

 

 

 

 

 

 

 

 

  • If not possible, a second chart with this concept alone (and not capture the full year value).

 

For reference, the values displayed are a simple sum of one column and the X-axis is based on yearly bins of a column with daily dates.

 

Does anyone have any suggestion?

 

Thanks,

OF

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI Anonymous,

     

    First, stacked/clustered column charts not support secondary y axis, current line and stacked column chart/clustered column charts support secondary y axis which can only used with line value field.

     

    Second, when you enable legend on stacked/clustered column charts, it is impossible to use multiple value fields. You can only display current year or previous year values base on date column.

     

    Sample measure formulas to create different visuals:

    Current=
CALCULATE (
    SUM ( Table[Amount] ),
    VALUES ( Table[Date] ) 
)


Previous Year=
CALCULATE (
    SUM ( Table[Amount] ),
    SAMEPERIODLASTYEAR ( VALUES ( Table[Date] ) )
)

    Notice: dax time intelligence functions are fixed and not support apply on complex filters and advanced customize, if your scenario is over their limit, please try to use date function with filters to manually coding conditions and point out date range.

     

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI Anonymous,

     

    First, stacked/clustered column charts not support secondary y axis, current line and stacked column chart/clustered column charts support secondary y axis which can only used with line value field.

     

    Second, when you enable legend on stacked/clustered column charts, it is impossible to use multiple value fields. You can only display current year or previous year values base on date column.

     

    Sample measure formulas to create different visuals:

    Current=
CALCULATE (
    SUM ( Table[Amount] ),
    VALUES ( Table[Date] ) 
)


Previous Year=
CALCULATE (
    SUM ( Table[Amount] ),
    SAMEPERIODLASTYEAR ( VALUES ( Table[Date] ) )
)

    Notice: dax time intelligence functions are fixed and not support apply on complex filters and advanced customize, if your scenario is over their limit, please try to use date function with filters to manually coding conditions and point out date range.

     

    Regards,

    Xiaoxin Sheng