Active Contracts Count by Year/Month

[Asfaa5]

Hello Team,

im new to Power Bi. I need a help on Dax Measure calculation of Active Contracts Count by Year/Month.

I have a Table "Sales" with 7,65,000 data. Sales Table contains "Policy Number", "Contract Start Date", "Contract End Date".

Need output as below :

Year	Month	Active Count
2019	Jan	4000
	Feb	4500
	Mar	5000
	Apr	5500
	May	6000
	Jun	6500
	Jul	7000
	Aug	7500
	Sep	8000
	Oct	8500
	Nov	9000
	Dec	9500
2020	Jan	9800
	Feb	10100
	Mar	10000
	Apr	11000
	May	12000
	Jun	11500
	Jul	10000
	Aug	9000
	Sep	7500
	Oct	7000
	Nov	6500
	Dec	6000
2021	Jan	5500
	Feb	5000
	Mar	4500
	Apr	4000
	May	3500
	Jun	3000
	Jul	2500
	Aug	2000
	Sep	1500
	Oct	1000
	Nov	100
	Dec	10

Can anyone help me on this.

[Anonymous]

Usually in cases like this the best is to create a separate date table contaning all dates covering the time period you want to analyze. The table should not be connected to the data table with any relations.

Then you create a measure something like this:

Active Contracts = CALCULATE(
  COUNTROWS('Sales'),
  FILTER('Sales', 'Sales'[Contract Start Date] <= MAX('Dates'[Date]) && 'Sales'[Contract End Date] >= MIN('Dates'[Date]))
)

I have assumed each row has one contract, so COUNTROWS('Sales') is the number of contracts.

Finally, use year / month / date from the dates table in combination with the new measure.

[Anonymous · Accepted solution]

Hi @Asfaa5 

Create one date table.

Date=Calender(DATE (2010, 1, 1), DATE (2020, 12, 31)).

Then create year and month column in the date table.

Then create measure suggested by @Anonymous .

And add year, month and measure to your table visual.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

What is the required logic for active contracts?

Is it where end date in blank() or 12/31/9999?

New measure=Calculate(Count[Table[Contract]),Filter(table,Table[end_Date]='12/31/9999')

Please modify filter condition as per your active contracts logic.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Asfaa5]

Hi. Thanks for your revert.

Logic is for Showing How many Contract are going to be active in Jan-20, Feb-20, Mar-20, like this till Contract Expire completely.

There is no blank in Contract End Date. Last date is till 31/12/2027.