Forum Discussion
Help needed graphing a count between a date range
parry2k, you have been a tremendous help today sir. Thank you for your patience.
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
- JLaine8 years agoHelper I
Not fully, no. This is still an open item.
- dipique8 years agoNew Member
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:
If you've figured out a way to do what you needed without the full expansion, please let me know.
- dipique8 years agoNew Member
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.