Pending Contract for approval by month

[CelsoJ]

Hi,

I have the table below and wanted to create a measure for how many contracts were/are pending in our pipeline by month/quarter. I have a separate Calendar table on my BI called DATETABLE.

By "Pending" I mean any contracts with a Start Date that did not have a End Date within a certain month. For example:

How many clients in March had a Start Date but no end date by the 31st of that month? And so on.

Client	Start Date	Signed Date
Client 1	22/06/2023	09/08/2023
Client 2	19/05/2023	03/08/2023
Client 3	21/06/2023
Client 4	07/12/2022	18/05/2023
Client 5	05/05/2023	26/07/2023
Client 6	19/06/2023
Client 7	25/10/2022	08/06/2023
Client 8	10/05/2023	06/07/2023
Client 9	21/06/2023	07/08/2023
Client 10	20/06/2023
Client 11	27/10/2022	28/04/2023
Client 12	15/11/2022	08/06/2023
Client 13	23/03/2023
Client 14	17/05/2023	21/07/2023
Client 15	18/11/2022	02/05/2023
Client 16	01/02/2023	28/03/2023
Client 17	11/05/2023	18/07/2023
Client 18	26/06/2023	18/07/2023
Client 19	13/12/2022
Client 20	12/08/2022	11/05/2023
Client 21	31/10/2022	26/07/2023
Client 22	10/01/2023	05/07/2023
Client 23	26/04/2023
Client 24	22/02/2023	31/05/2023
Client 25	15/11/2022	08/06/2023
Client 26	20/06/2023
Client 27	20/06/2023
Client 28	07/03/2023	09/06/2023
Client 29	12/05/2023
Client 30	13/12/2022	24/04/2023
Client 31	16/02/2023
Client 32	01/08/2022
Client 33	18/04/2023	19/06/2023
Client 34	17/05/2023	25/07/2023
Client 35	27/04/2023	09/08/2023
Client 36	04/10/2022	02/05/2023
Client 37	18/11/2022
Client 38	03/03/2023	11/07/2023
Client 39	23/03/2023
Client 40	26/04/2023	20/07/2023
Client 41	20/06/2023
Client 42	11/11/2022	03/05/2023
Client 43	26/10/2022	05/07/2023
Client 44	10/05/2023	26/07/2023
Client 45	25/06/2023	10/08/2023
Client 46	10/01/2023	28/04/2023
Client 47	18/04/2023
Client 48	24/02/2022
Client 49	21/12/2022	07/07/2023
Client 50	28/02/2023	21/06/2023
Client 51	24/03/2023	07/08/2023
Client 52	05/05/2023
Client 53	09/01/2023
Client 54	26/05/2023
Client 55	10/05/2023	22/06/2023
Client 56	18/10/2022	13/06/2023
Client 57	12/05/2023
Client 58	12/06/2023
Client 59	09/03/2023	26/06/2023
Client 60	26/04/2023	29/06/2023
Client 61	02/06/2023	07/07/2023
Client 62	23/06/2023
Client 63	05/05/2023
Client 64	28/04/2023
Client 65	04/04/2023	21/06/2023
Client 66	09/02/2023	02/06/2023
Client 67	02/03/2023
Client 68	17/05/2023	10/08/2023

Thank you!

[v-yangliu-msft · Accepted solution]

Hi  @CelsoJ ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _selectmonth=SELECTEDVALUE('Date'[Date].[MonthNo])
var _selectyear=SELECTEDVALUE('Date'[Date].[Year])
var _mindate=DATE(_selectyear,_selectmonth,1)
var _maxdate=EOMONTH(_mindate,0)
return
IF(
    MAX('Table'[Start Date])>=_mindate&&MAX('Table'[Start Date])<=_maxdate&&MAX('Table'[Signed Date])=BLANK(),1,0)

Count =
COUNTX(ALLSELECTED('Table'),'Table'[Client])

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ritaf1983]

Hi @CelsoJ 
Can you please explain what you mean by "pending" ?

Does it refer to what is active on the selected date?
In other words, do you mean that the selected date is greater than your start date and less than your end date?

[CelsoJ]

Hi Sorry, good question.

By "Pending" I mean any contracts with a Start Date that did not have a End Date within a certain month. For example:

How many clients in March had a Start Date but no end date by the 31st of that month?

I have amended my question to reflect this clarification.

[Ritaf1983]

Sorry, I still don't understand.
Do you choose a specific period or month?

It will be helpful if you can provide an example of the highly desired result...

[CelsoJ]

I wanted to be able to track it month-by-month with a Line Tracker, example of result:

Contracts that did not have an end date by end-of-march from that list were:

CLIENTS     Start Date    Signed Date

Client 3	21/06/2023
Client 6	19/06/2023
Client 10	20/06/2023
Client 13	23/03/2023
Client 19	13/12/2022
Client 23	26/04/2023
Client 26	20/06/2023
Client 27	20/06/2023
Client 29	12/05/2023
Client 31	16/02/2023
Client 32	01/08/2022
Client 37	18/11/2022
Client 39	23/03/2023
Client 41	20/06/2023
Client 47	18/04/2023
Client 48	24/02/2022
Client 52	05/05/2023
Client 53	09/01/2023
Client 54	26/05/2023
Client 57	12/05/2023
Client 58	12/06/2023
Client 62	23/06/2023
Client 63	05/05/2023
Client 64	28/04/2023
Client 67	02/03/2023

Which are blanks and also:

Client 16

01/02/2023

28/03/2023

The total count of contracts that were pending during that period would then be:

26

But I'd like to create a measure where I can, connecting this table with a Calendar table, have that tracked month-by-month.

[CelsoJ]

@Ritaf1983, forgot to @ you here.