Help needed graphing a count between a date range

[JLaine]

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.

[stretcharm]

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.

[JLaine]

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.

[parry2k]

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

[JLaine]

@parry2k, The data is not currently 'pivoted' (as far as I know); it is just the source SQL query results at the moment.  As for the "join with calendar (date dimension)"; please tell me more about this, as this is something new to me.

[parry2k]

i guess easier will be with sample pbix, let me see if i can put together something quickly for you. 

[parry2k]

you can download the solution here 

it uses your example serial number and showing by quarter.

[JLaine]

@parry2k, thank you for that sample.  I notice there are two data tables, with one being a calendar; did you have to manually create this data table?

[parry2k]

yes, there are many ways to create it, it is best practice to have continuous date (calendar) table in your model for time intelligence related analysis.

there is dax function called calendar which populate the table with provided date range, link for reference and rest of the columns are just simple formulas, it can also achieved by power query and there are several way to do this, here is link for you to test that. There are many topics and you can search on it.

[JLaine]

@parry2k, thank you very much for that; it makes way more sense now.

However, in the example, only one date field is used; the 'warranty date', but in my source data there are two date fields that impact the result; the start date and the end date; I don't see how to ensure the count on the [Calendar] date takes into concideration both the warranty start and end dates.  Can you help me understand this bit please?

[parry2k]

yes warranty date is calculated from date range from your sample.

so raw data is exactly the way your table is, serial number, start date, end date

and then it unpivot to get warrantly applicabale for each serial for each date, and count that. 

to see all this, go to query editor, and check the steps of "Table1", the first step is exactly the way the data your provided, and next steps do the transforamtion.

[JLaine]

@parry2k, looking in the Query editor, I see that the source data of 3 rows became 1251 rows after you added a custom column and expanded the range.  As my real data has over 10 million rows of data this expansion makes me very nervous.  Can you please explain it?

[parry2k]

That is surely a concern, will slow down the solution, not ideal in that case. Interesting. let's c what else we can do here.

[parry2k]

how granular you want to see this warranty? Month Level/Quarter level/Year level.

Current solution will get you to day level 

[tohare85]

@parry2k How would you use this solution to change it to monthly?

Thanks,

Tim

[parry2k]

@tohare85if you download the pbix file which i share in this thread, you can drop month name from your calendar table and you will get data at month level.

[tohare85]

@parry2k Sorry, I meant how can you make it to where the "each" keyword iterates across months and not a new record for each day? Daily will make the data set too large.

[JLaine]

@parry2k, at this point in time, I'm thinking that monthly granularity will probably be sufficient.  But if you are thinking of a chard by month, with count, know that I also need to seperate by product, and there are several thousand different [products] in the data set, which adds another level of complicaiton I did not want to start this thread with  (I'll be grouping those into product families and sub-families, to reduce the number of unique items to filter and chart)

[JLaine]

@parry2k, you have been a tremendous help today sir.  Thank you for your patience.

[v-huizhn-msft]

Hi @JLaine,

Have you resolved your issue? If you have, welcome to share your solution or mark the helpful/useful reply as answer. More people will benefit from here. Thanks very much.

Best Regards,
Angelia

[JLaine]

Not fully, no.  This is still an open item.