cancel
Showing results for
Did you mean:
Frequent Visitor

## Total Sales Variation Same Period Last Year

Hello,

My data model has sales data from the last 3 years. I'm trying to build a report with two charts (image below):

• Sales variation by month. Variation related to last month. (OK)
• Sales variation by month. Variation related to same period last year. (NOT OK)

Filters: Year (single select)

My Measures:

• Total Sales = SUMX( Sales, Sales[Total])
• Total Sales Same Period Last Year CALCULATE([Total Sales], FILTER(ALL('Calendar'), 'Calendar'[Year]=MAX('Calendar'[Year])-1), SAMEPERIODLASTYEAR('Calendar'[Data]))
• Variation Last Year %divide([Total Sales]-[Total Sales Same Period Last Year],[Total Sales],0)

In the green chart, filtering the year, the variation is ok, because the values of last month are included in the filter.

In the red chart, filtering the year (ex: 2021), it doesn't show the variation because the sales of same period last year are not included in the filter (2020). Plus, the chart is showing 2022 months variation.

Goal: I just want to show selected year months, with the variation from same period last year.

1 ACCEPTED SOLUTION
Community Support

@diogo_fernandes

To get the both last year value with slicer, you would need to create a distinct year table as the slicer.

1. Create a new table:

Slicer table = Distinct(Calendar[Year])

2. Create the 3 measures:

Total Sales = SUMX( Sales, Sales[Total])

Total Sales Same Period Last Year =  CALCULATE([Total Sales], FILTER('Calendar',  'Calendar'[Year]=SELECTEDVALUE('Slicer table'[Year])-1),SAMEPERIODLASTYEAR('Calendar'[Data]))

Variation Last Year % = divide([Total Sales]-[Total Sales Same Period Last Year],[Total Sales],0)

Best regards
Paul Zheng _ Community Support Team

If this post helps, please Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Community Support

@diogo_fernandes

To get the both last year value with slicer, you would need to create a distinct year table as the slicer.

1. Create a new table:

Slicer table = Distinct(Calendar[Year])

2. Create the 3 measures:

Total Sales = SUMX( Sales, Sales[Total])

Total Sales Same Period Last Year =  CALCULATE([Total Sales], FILTER('Calendar',  'Calendar'[Year]=SELECTEDVALUE('Slicer table'[Year])-1),SAMEPERIODLASTYEAR('Calendar'[Data]))

Variation Last Year % = divide([Total Sales]-[Total Sales Same Period Last Year],[Total Sales],0)

Best regards
Paul Zheng _ Community Support Team

If this post helps, please Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Hello @V-pazhen-msft ,

Total Sales Same Period Last Year measure is returning zero.

TotalSales SPLY = CALCULATE([TotalSales], FILTER('Calendar', 'Calendar'[Year]=SELECTEDVALUE('Slicer Table'[Year])-1),SAMEPERIODLASTYEAR('Calendar'[Date]))

Frequent Visitor

Hello @V-pazhen-msft ,

I managed to fixed Total Sales SPLY measure this way:

TotalSales SPLY = CALCULATE([TotalSales], SAMEPERIODLASTYEAR('Calendar'[Date]))

Using the Table Slicer year in the filter with single selection, now it shows the correct variation.

Super User

@diogo_fernandes Perhaps you can try this:

Total Sales Same Period Last Year =
VAR _lastdatewithdata =
LASTNONBLANK ( 'Calendar'[Date], [Total Sales] )
RETURN
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR (
FILTER ( VALUES ( 'Calendar'[Date] ), 'Date'[Calendar] <= _lastdatewithdata )
)
)

Respectfully,
DataZoe

See my reports and blog at https://www.datazoepowerbi.com/

Frequent Visitor

That worked out only for hidding next year months - when I select 2021, the 2022 months aren't shown.

However, I have the same problem with showing variation on same period last year with just one year selected.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!