Multi year Line chart. the problem is that the dates data are in one Column!

[Anonymous]

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

[amitchandak · Accepted solution]

Drag date to both Axis and Legend and choose the hierarchy level you want 

[Anonymous]

Thanks, I have just figured it out 30 minutes before you wrote. 😊

[Anonymous]

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!

[Anonymous]

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))

[Anonymous]

Note that we use a monthly data, hence the dates are, 01/01/2021, 01/02/2021,...