Forecast # of contracts based on Start & End Date

[Mwoody_05]

I have a table of contracts with Start and End dates and I have a dates table
I know how to get the count of current active contracts but how would I get the number of contracts that will be active next month, next year, 2 years from now?

Contract id	start_date	end_date	created	Index
115	7-Jun-21	7-Jun-24	6/7/2021 19:52	1
103	1-Mar-21	1-Mar-24	5/11/2021 13:23	1
255	29-Jan-22	29-Jan-24	1/29/2022 17:58	1
252	28-Jan-22	28-Jan-24	1/29/2022 1:03	1
249	24-Jan-22	24-Jan-24	1/24/2022 15:41	1
66	21-Jan-21	21-Jan-24	1/21/2021 21:02	1
251	21-Jan-22	21-Jan-24	1/27/2022 16:59	1
245	21-Jan-22	21-Jan-24	1/21/2022 15:46	1
244	18-Jan-22	18-Jan-24	1/18/2022 21:23	1
239	11-Jan-22	11-Jan-24	1/12/2022 3:28	1
238	11-Jan-22	11-Jan-24	1/11/2022 17:23	1
253	11-Jan-22	11-Jan-24	1/29/2022 17:54	1
235	7-Jan-22	7-Jan-24	1/7/2022 12:27	1
236	7-Jan-22	7-Jan-24	1/7/2022 16:15	1
232	6-Jan-22	6-Jan-24	1/6/2022 20:23	1
233	5-Jan-22	5-Jan-24	1/6/2022 21:00	1
224	5-Jan-22	5-Jan-24	1/6/2022 15:06	1
231	5-Jan-22	5-Jan-24	1/6/2022 20:23	1
234	4-Jan-22	4-Jan-24	1/7/2022 3:57	1
230	4-Jan-22	4-Jan-24	1/6/2022 20:22	1
215	3-Jan-22	3-Jan-24	1/6/2022 14:43	1
216	3-Jan-22	3-Jan-24	1/6/2022 14:44	1
218	29-Dec-21	28-Dec-23	1/6/2022 14:46	1
217	27-Dec-21	26-Dec-23	1/6/2022 14:45	1
229	22-Dec-21	22-Dec-23	1/6/2022 17:12	1
213	22-Dec-21	22-Dec-23	1/6/2022 14:43	1
227	21-Dec-21	21-Dec-23	1/6/2022 15:13	1
211	20-Dec-21	20-Dec-23	1/6/2022 14:42	1
221	18-Dec-21	18-Dec-23	1/6/2022 15:05	1
226	17-Dec-21	17-Dec-23	1/6/2022 15:11	1
204	15-Dec-21	15-Dec-23	12/15/2021 19:08	1
203	15-Dec-21	15-Dec-23	12/15/2021 14:26	1
201	14-Dec-21	14-Dec-23	12/14/2021 17:16	1
199	13-Dec-21	13-Dec-23	12/13/2021 17:12	1
90	5-Dec-20	5-Dec-23	4/5/2021 15:58	1
223	1-Dec-21	1-Dec-23	1/6/2022 15:06	1
189	1-Dec-21	30-Nov-23	12/1/2021 18:04	1
186	29-Nov-21	29-Nov-23	12/1/2021 17:56	1
197	24-Nov-21	24-Nov-23	12/2/2021 15:34	1
185	17-Nov-21	17-Nov-23	12/1/2021 17:56	1
193	16-Nov-21	16-Nov-23	12/1/2021 21:26	1
192	9-Nov-21	9-Nov-23	12/1/2021 21:25	1
196	9-Nov-21	9-Nov-23	12/2/2021 15:33	1
182	5-Nov-21	5-Nov-23	11/5/2021 18:42	1
181	5-Nov-21	5-Nov-23	11/5/2021 14:49	1
191	2-Nov-21	2-Nov-23	12/1/2021 21:24	1

[V-lianl-msft]

Hi @Mwoody_05 ,

If you have time data in your data source, you can use the forecasting feature. Just select a visual, then expand the Forecast section of the Analytics pane.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane#apply-forecasting 

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

[Mwoody_05]

Thank you @VahidDM however is there a way to do it in the same measure? Looking to trend it in a line graph. Need the line graph to project into the future

[VahidDM]

@Mwoody_05 

Can you create that result chart in Excel and share that here to have a better understanding of your request?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[VahidDM]

Hi @Mwoody_05 

Try these measures:

No. of Active Contracts next month = 
Var _D = today()
Var _NMS = DATE(Year(_D),MONTH(_D)+1,1)
Var _NME = DATE(Year(_D),MONTH(_D)+2,1)-1
return
CALCULATE(
    COUNT('Table'[Contract id]),
    filter(
        all( 'Table'),
        'Table'[start_date]<=_NME
        && 'Table'[end_date]>=_NMS
    )
)

No. of Active Contracts next Year = 
Var _D = today()
Var _NMS = DATE(Year(_D)+1,1,1)
Var _NME = DATE(Year(_D)+2,1,1)-1
return
CALCULATE(
    COUNT('Table'[Contract id]),
    filter(
        all( 'Table'),
        'Table'[start_date]<=_NME
        && 'Table'[end_date]>=_NMS
    )
)

No. of Active Contracts 2 Year later = 
Var _D = today()
Var _NMS = DATE(Year(_D)+2,1,1)
Var _NME = DATE(Year(_D)+3,1,1)-1
return
CALCULATE(
    COUNT('Table'[Contract id]),
    filter(
        all( 'Table'),
        'Table'[start_date]<=_NME
        && 'Table'[end_date]>=_NMS
    )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/