Forum Discussion
Multi year Line chart. the problem is that the dates data are in one Column!
Drag date to both Axis and Legend and choose the hierarchy level you want
Create a column for year for filtering/slicing
Column Year = YEAR('table'[Date])Then create a measure for total sales
Total Sales = SUM('table'[sales])Then create a measure (or several measures if you want to look at multiple years) that adjusts the date range that [Total Sales] is looking at.
Total Sales minus X years = CALCULATE([Total Sales], DATEADD('table'[date], -X, YEAR)Then if you put a slicer or filter on your page using Column Year, Total Sales will show whatever year you have selected and Total Sales minus X years will be adjusted accordingly.
For example, if you select 2011 on your filter/slicer, Total Sales will show all sales for 2011 and Total Sales minus X years will show sales for last year (assuming x = 1)
If this works for you, please mark my answer as solved. Let me know if you have more questions. Cheers!
Hi! I think this quite unrelated but I tried using your formula but there is an error with how the data is presented. I am only using a card as a visualization
Using the DAX formula, total sum last year is blank if only one year is selected. But when consecutive years are selected, it returns a value. It also doesn't return a value if two non-consecutive years are selected.
Formula I used
Quantity last Year = CALCULATE([Total Water Consumption],DATEADD('Water Consumption'[DATE],-1,YEAR))Note that we use a monthly data, hence the dates are, 01/01/2021, 01/02/2021,...