Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
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
MFelix
Super User
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:

Relation.png

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.

 

dates_count.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
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:

Relation.png

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.

 

dates_count.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

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?

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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