Forum Discussion
Multi year Line chart. the problem is that the dates data are in one Column!
Hello Everyone,
Newbie to PBI here, I'm trying to create a line chart that shows sales and 2 lines of 2 different years. In fact, it's a sale comparison between 2 years (2010/2011) for the same period(Jan1, Jan2....., Jan 12). In other words, I would Like to have a line chart with the following:
- X Axis (Jan1, Jan2....., Jan 12).
- Y Axis (sales).
- 2 lines ( a line for 2010 and a line for 2011).
Please let me know if you need any more info that could help solve this issue.
Thanks a lot in advance
Drag date to both Axis and Legend and choose the hierarchy level you want
5 Replies
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))