cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Count the number of Start and end dates in a given month, quarter, etc.

Hi, I have a table of contracts with start and end dates and need to plot on a graph the number of contracts that end or start in a given month. Any ideas?

1 ACCEPTED SOLUTION
Super User

Hi @Anonymous,

Taking into account that you have two date columns first of all I would create a calendar table (tutorial calendar table).

1 - Create a two non-active relationships between the Calendar table and your Start and End Date:

2 - Add the following measures:

```Start_Date_Count =
CALCULATE (
COUNT ( Contracts[Contract] ),
USERELATIONSHIP ( 'Calendar'[Date], Contracts[Start_Date] )
)

End_date_Count =
CALCULATE (
COUNT ( Contracts[Contract] ),
USERELATIONSHIP ( 'Calendar'[Date], Contracts[End_Date] )
)```

If need only one measure create the following measure:

```Total_Date_Count =
CALCULATE (
COUNT ( Contracts[Contract] ),
USERELATIONSHIP ( 'Calendar'[Date], Contracts[Start_Date] )
)
+ CALCULATE (
COUNT ( Contracts[Contract] ),
USERELATIONSHIP ( 'Calendar'[Date], Contracts[End_Date] )
)```

3 - Create your visuals with the desired measure.

See below a sample with my table, the visuals with start and end month calculation and another with the measures I created to see the result is the same, with the Calendar table you can create slicers that will allow you to show only the information you want.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

3 REPLIES 3
Super User

Hi @Anonymous,

Taking into account that you have two date columns first of all I would create a calendar table (tutorial calendar table).

1 - Create a two non-active relationships between the Calendar table and your Start and End Date:

2 - Add the following measures:

```Start_Date_Count =
CALCULATE (
COUNT ( Contracts[Contract] ),
USERELATIONSHIP ( 'Calendar'[Date], Contracts[Start_Date] )
)

End_date_Count =
CALCULATE (
COUNT ( Contracts[Contract] ),
USERELATIONSHIP ( 'Calendar'[Date], Contracts[End_Date] )
)```

If need only one measure create the following measure:

```Total_Date_Count =
CALCULATE (
COUNT ( Contracts[Contract] ),
USERELATIONSHIP ( 'Calendar'[Date], Contracts[Start_Date] )
)
+ CALCULATE (
COUNT ( Contracts[Contract] ),
USERELATIONSHIP ( 'Calendar'[Date], Contracts[End_Date] )
)```

3 - Create your visuals with the desired measure.

See below a sample with my table, the visuals with start and end month calculation and another with the measures I created to see the result is the same, with the Calendar table you can create slicers that will allow you to show only the information you want.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

Thanks so much. Works a charm. I then tried to use a waterfall charter with a measure of Runners difference = [Starters]-[Leavers]. This works but doesn't filter by User/Sales person. Any ideas?

Super User

Hi @Anonymous,

How does the User/Sales person connects to this measures? Is it on a separeted table or on the same table?

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors