Count of Rows by Month that Fall Between Certain Dates

This works (at least it gets the same results as your expected results. Note I am in Australia so our date format is dd/mm/yyyy!

1. Get data (Contracts)

Create a pair of measures called Mindate and Maxdate on Contracts 

3. Create a calendar table as follows;

ContractDateTable = filter(

 

 

//************************************************************

// Date range below. This will generate a table with a [Date] column

//************************************************************

CALENDAR(DATE(YEAR(Contracts[Mindate]),MONTH(Contracts[Mindate]),DAY(Contracts[Mindate])),

DATE(Year(Contracts[MaxDate]),month(Contracts[MaxDate]),day(Contracts[MaxDate])))

 

 

,day([Date]) = 1

)

 

This gets a table with the min contract start date and max contract end date normalised to the 1st of each month.

 

4. Create 2 columns on Contracts

 

ContractStartMonth = Date(YEAR(Contracts[Start Date]),month(Contracts[Start Date]),1)

ContractEndMonth = eomonth(Contracts[End Date],0)

 

5. Create a column on ContractDateTable as follows;

 

ActiveContracts = CountRows(FILTER(Contracts,Contracts[ContractStartMonth]<=[Date]&&Contracts[ContractEndMonth] >=[Date]))

 

Now in the ContractsDateTable you have a column called ActiveContracts that shows how many contracts were active during that month.

 

My results are;

 

 

 

Stan

 

 

 

 

 

Hi,

You may download my PBI file from here.

Hope this helps.