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

[Anonymous]

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? 

[MFelix · Accepted solution]

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

[Anonymous]

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?

[MFelix]

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