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.

[dipique]

I was able to figure this out, details are on this thread.

First, I created a calendar that contains only the first day of each month, and only dates that current or in the past:

Program Active Months = FILTER(CALENDAR(DATE(2018,1,1),DATE(2025,12,31)),AND(DAY([Date])=1, TODAY()>=[Date]))

Then, I used this to produce the table I wanted which showed me enrollee count by client and month:

Active Enrollees = 
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            Enrollment,
            'Program Active Months'
        ),
        [Date] >= [Effective_Date__c] &&
        [Date] <= Enrollment[End Date]
    ),
    "Id", Enrollment[Id],
    "Date", [Date],
    "Account", [Account Name]
)
RETURN GROUPBY(tmpTable,[Date],[Account],"Count",COUNTX(CURRENTGROUP(),[Id]))

I hope this helps anyone else in a similar situation.

[dipique]

I just wanted to let you know that I have just opened a similar query about the last part of your question: how to expand by month instead of by day. It's located here:

http://community.powerbi.com/t5/Desktop/Column-formula-to-expand-a-date-range-by-month-instead-of-by...

If you've figured out a way to do what you needed without the full expansion, please let me know.

[parry2k]

I hope you have the solution what you are looking for, that's what all matter 🙂