Forum Discussion
Help needed graphing a count between a date range
I am new to using Power BI, and am looking for some guidance with the following:
I have a large SQL data set (larger than I can work with in Excel) contianing many data columns including the following:
[Unique Serial Number], [Product Model], [Product Family], [Date sold], [Warranty Expiration Date], [Sold to Country]
I am trying to output a timeline graph with the following:
- User-selection for [product family] and [Product Model] to view.
- Y-axis for quantity (count) of product.
- X-axis for user-selectable date range and period breakdown (daily, weekly, monthly, quarterly, yearly, etc.)
- Data lines for each [Product Model] contianing a sum-total quantity of [Unique Serial Number], where the product is within its warranty period; that is, the date on the graph's X-axis is between the [date Sold] and the [Warranty Expiration Date], doe each [Unique Serial Number]
- Data line thickness and/or color-coding by [Sold to Country]
The objective of this is to visually 'see' how many of which product are still within their warranty period, over time.
Now I think I know how to place a graph and add a slicer for user-selection, but I don't know how to do the rest, like making the X-axis be a selectable date range, as opposed to dates from a colum in the source-data. I also don't know how to graph the count of [Unique Serial Number] within the two start and end dates of [Date sold], an [Warranty Expiration Date].
If anyone in the community has sufficient knowledge, experience, and patience to help this Power BI noob with this, it would be appreciated.
Tahnk you.
23 Replies
Sounds fairly straight forward.
Watch the guided learning - getting started videos.
https://powerbi.microsoft.com/en-us/learning/
1) Get Data
2) Connect to SQL
3) Select [Unique Serial Number], [Product Model], [Product Family], [Date sold], [Warranty Expiration Date], [Sold to Country] from your SQL DB. PowerBI can handle much more data than Excel so try without aggregationm but if your data is too big try grouping in SQL first.
4) Ensure you dates are Date types
5) add a 3 slicers for Product Model, Family and Date. Dates will default to a range slicer.
6) add a line graph with date on the axis ,SerialNo in values and Model in legend. Serial no will probably default to count. If not change it in the values by right mouse menu on the field.
Drill into the dates to see more detail or remove the hierarchy if you don't like it (right mouse on the date in the axis).
Get to this point then we can look at the other elements. Not sure about the warranty expiration date, Is this based on now or a selected date?
Also do you want a different line graph for country?
Maybe a scatter chart is better as you can have lost of deteail e.g. grouping, position, size and colour.
Thanks stretcharm, but that does nto get me to the result I need.
As an example, if I have the following 3 data rows...
[Serial], [Sale date], [expiry date]
abc123, 1-May-2016, 1-May-2017 (a 1 year warranty)
efg456, 12-July-2016. 12-Dec, 2016 (a 6-month warranty)
hij789, 25-Oct-2016, 25-Oct-2018 (a 2 year warranty)
Then the quarterly chart of 'units in warranty'would need to result in something like this...
2016 Q1 = 0 Units in warranty
2016 Q2 = 1 unit in warranty
2016 Q3 = 2 units in warranty
2016 Q4 = 3 units in warranty
2017 Q1 = 2 units in warranty
2017 Q2 = 2 units in warranty
2017 Q3 = 1 units in warranty
2017 Q4 = 1 units in warranty
2018 Q1 = 1 units in warranty
2018 Q2 = 1 units in warranty
2018 Q3 = 1 units in warranty
2018 Q4 = 1 units in warranty
2019 Q1 = 0 units in warranty
... which does not happen if I use 'step 6' in your response, as you described it.
basically you need to unpivot your date range and then join with calendar (date dimension) to get the count by month, quarter or year.
Let me know if you need help on how to do this
