Forum Discussion
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.
Anonymous
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 TeamIf this post helps, please Accept it as the solution to help the other members find it more quickly.
6 Replies
- DataZoe
Microsoft Employee
Anonymous 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 )
)
) Anonymous
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 TeamIf this post helps, please Accept it as the solution to help the other members find it more quickly.
I want to ask you: what's the point of this part: "...'Calendar'[Year]=MAX('Calendar'[Year])-1...", cause I understand that the SAMEPERIODLASTYEAR already take the last year.
Thanks a lot.
