Forum Discussion
Count of Rows by Month that Fall Between Certain Dates
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 |
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 ContractsContractStartMonth = 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
2 Replies
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 ContractsContractStartMonth = 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
Super User