Column formula to expand a date range by month instead of by day

[dipique]

I currently have a table with a list of contracts, start date, and termination date. I want a chart that shows active active contracts by month. So if my current data is:

Contract Name	Start Date	Termination Date
Customer 1	3/1/2018	6/30/2018
Customer 2	5/1/2018	9/30/2018
Customer 3	2/1/2018	10/31/2018

I need to be able to produce a table like this:

Month	Active Contracts
Jan 2018	0
Feb 2018	1
Mar 2018	2
Apr 2018	2
May 2018	3
Jun 2018	3
Jul 2018	2
Aug 2018	2
Sep 2018	2
Oct 2018	1
Nov 2018	0

The summarizing is all simple; the real problem was expanding the date ranges to display the months in between.

I found that I can use a formula to expand the range into the dates between by creating a new column with the custom formula:

{[Start Date]..[Termination Date]}

I then group by month to produce the visualization I want. The problem is that contracts last several years, which means that (for example) 200 contracts with a length of 10 years each (or 3,650 days) results in 730,000 records. This quickly gets out of hand, and performance is definitely an issue.

I would like to modify the formula to expand the date range by month instead of by day. For example, it could produce a list of only the first day of every month instead of every day in every month. I could filter the table, but that has all the same issues I am trying to deal with.

Help please! Thank you!

[Greg_Deckler · Accepted solution]

Please see my Open Tickets Quick Measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

[Greg_Deckler · Accepted solution]

Please see my Open Tickets Quick Measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

[dipique · Accepted solution]

Thank you so much! This got me far enough to get a solution working. I created the calendar using this formula:

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

This got me the first day of every month between the date of our product launch and the current 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]))

Now I have just what I need! Thank again for your help.