Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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
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,
@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
Thanks a lot! apprecaite the help. this worked
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |