cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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). 
    Capture.PNG

     

 

 

 

 

 

 

 

 

 

 

 

  • 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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors