Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
My data model has sales data from the last 3 years. I'm trying to build a report with two charts (image below):
Filters: Year (single select)
My Measures:
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.
Solved! Go to Solution.
@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 Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
@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 Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
Hello @V-pazhen-msft ,
Total Sales Same Period Last Year measure is returning zero.
Hello @V-pazhen-msft ,
I managed to fixed Total Sales SPLY measure this way:
@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 )
)
)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |