line graph - adding additional line based on same table data

[dalemoy]

Hello community.  

i'm a fairly novice user so please forgive my basic knowledge.

I have a line graph that shows quantity of stock sold every day/week/month/quarter/year which is filtered by a date slicer. 

What I would like to do is have a line added for each year, (2015,2016,2017 etc) so I can see the trends in ordering. 

My data is from an SQL database we have on premise and to keep the functionality the way I currently have it, there are 6 tables that have relationships to each other. 

as far as I can tell, I would need to either add or seperate the data into seperate tables based on date, which would mean I would loose my necessary table relationships. 

please see below for what I currently have, and thank you in advance for any advice/help you can provide.

[MikeChicago]

For a clustered column chart what I used was Axis FullDate made it a hierarchy and X-out evrything but month. Then Legend Fulldate hierarchy X-out everything but year. Muti-select slicer. try that and see if it gives you the grouping you need for the line chart.

[jthomson]

Try looking into pushing your existing measures into new ones that filter your data by year, you should then be able to plot a measure for 2017, 2016, 2015 etc in your graph. There's quite a few posts discussing this, try looking at (for example) https://community.powerbi.com/t5/Desktop/Year-over-Year-in-line-chart/td-p/13691

[dalemoy]

Hi jthomson,

Thanks for replying so quickly! 

I am familiar with creating new measures, but my DAX is quite poor, how would I formulate this with my dataset in mind? 

This formula would have to take into account the relationships I have in mind - i.e clicking on a particular stock line or filtering by stock type would ultimately effect the graph. 

if it helps, please find an example of the dataset i'm working with below.

Table 1: SORDER

SORDER_ID    DATE_CREATED

10048	06/03/2015 00:00:00
10061	07/01/2015 00:00:00
10385	07/01/2015 00:00:00

table 2:  SORDER_ITEM

SORDER_ID  STOCK_ID   STOCK_DESC                                             QTY_ASSIGNED

29309	145	6mm Biopsy Punch Disposable Qty:10	2
29309	148	Disposable CURETTE 7mm Qty:10	2
29311	240	M1 Main Filter for Acu-Evac IE-2	1

Table 3: STOCK

STOCK_IDSTYPE_ID

-1	3
1	1
10	6
100	10

Table 4: STOCK_TYPE

STYPE_ID   STOCK TYPE DESC

1	[System & Misc Types] System & Miscellaneous
10	[Surgical] Tourniquet & Splints
11	[Surgical] Punches & Curettes

I really appreciate any help you give to a noob like me. 

Thanks again,

[v-yuezhe-msft · Accepted solution]

@dalemoy,

Do you want to add line to the existing line chart representing different years ?If so, create a year column using dax below, then drag the column to Legend of the line chart. There is an example for your reference.

Year= YEAR(Table[Date])


If the above steps don't help, please describe more details about what fields you use to create relationship among the tables and what field you want to add as X-axis of line chart.

Regards,
Lydia

[dalemoy]

Thanks a lot! apprecaite the help. this worked