Count of Rows by Month that Fall Between Certain Dates

[Anonymous]

I'm trying to create a measure that provides a count of contracts that are active at any time in a given month.

Right now each row of data is a single contract so I can just use countrows() for the counting but I'm trying to figure out the active part. 

You can see below a simple version of the data I have along with the results I'm trying to put together. I also have a Primary Date Table which I link to the Contract Start Date. This table contains every date possible along with a year-month column for the year month portion of the matrix. 

Any help would be appreciated. 

Contract Name	Start Date	End Date
Contract 1	10/1/2018	12/31/2018
Contract 2	10/15/2018	1/15/2019
Contract 3	10/31/2018	2/1/2019
Contract 4	1/1/2019	1/30/2019
Contract 5	1/28/2019	4/1/2019

Results: 

	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19
Active Contracts	3	3	3	4	2	1	1

[Anonymous · Accepted solution]

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 

Mindate = min(Contracts[Start Date])

MaxDate = max(Contracts[End Date])

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

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 

Mindate = min(Contracts[Start Date])

MaxDate = max(Contracts[End Date])

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