Help needed graphing a count between a date range

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

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

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?

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.

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

Current solution will get you to day level 

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)

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

Thanks,

Tim

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.

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.

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

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

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-day/m-p/414995#M190505

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

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.