Count no of record with date range within a period

[sunnyleungthf]

I think i need help on solving the problem, it seems not that complex but i just cannot think of how to make it.

I got a table containing records of contracts with contract start date and contract end date. Together with a date table, i would like to show in a bar chart the no. of active contracts in each month. Whether i should use measure to make it or need to create a table to handle this?

Thanks~~

[dearwatson · Accepted solution]

So you will need a date to filter on... in this example I created a Calendar table with Calendar = CALENDARAUTO()

No need for any relation ships - you will use the calendar to act as an unrelated parameter.. its just to get a date to check against.

Then it is just one simple measure:

Active Contracts = CALCULATE(Contracts[Contracts],FILTER(Contracts,(Contracts[Contract Start]<=LASTDATE('Calendar'[Date])&&[Contract End]>=FIRSTDATE('Calendar'[Date]))))

The legendary Chris Campbell posted this solution on the legendary powerpivotpro.com blog a while back:

https://powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbe...

Cheers

Greg Nash