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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Mwoody_05
Frequent Visitor

Forecast # of contracts based on Start & End Date

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 idstart_dateend_datecreatedIndex
1157-Jun-217-Jun-246/7/2021 19:521
1031-Mar-211-Mar-245/11/2021 13:231
25529-Jan-2229-Jan-241/29/2022 17:581
25228-Jan-2228-Jan-241/29/2022 1:031
24924-Jan-2224-Jan-241/24/2022 15:411
6621-Jan-2121-Jan-241/21/2021 21:021
25121-Jan-2221-Jan-241/27/2022 16:591
24521-Jan-2221-Jan-241/21/2022 15:461
24418-Jan-2218-Jan-241/18/2022 21:231
23911-Jan-2211-Jan-241/12/2022 3:281
23811-Jan-2211-Jan-241/11/2022 17:231
25311-Jan-2211-Jan-241/29/2022 17:541
2357-Jan-227-Jan-241/7/2022 12:271
2367-Jan-227-Jan-241/7/2022 16:151
2326-Jan-226-Jan-241/6/2022 20:231
2335-Jan-225-Jan-241/6/2022 21:001
2245-Jan-225-Jan-241/6/2022 15:061
2315-Jan-225-Jan-241/6/2022 20:231
2344-Jan-224-Jan-241/7/2022 3:571
2304-Jan-224-Jan-241/6/2022 20:221
2153-Jan-223-Jan-241/6/2022 14:431
2163-Jan-223-Jan-241/6/2022 14:441
21829-Dec-2128-Dec-231/6/2022 14:461
21727-Dec-2126-Dec-231/6/2022 14:451
22922-Dec-2122-Dec-231/6/2022 17:121
21322-Dec-2122-Dec-231/6/2022 14:431
22721-Dec-2121-Dec-231/6/2022 15:131
21120-Dec-2120-Dec-231/6/2022 14:421
22118-Dec-2118-Dec-231/6/2022 15:051
22617-Dec-2117-Dec-231/6/2022 15:111
20415-Dec-2115-Dec-2312/15/2021 19:081
20315-Dec-2115-Dec-2312/15/2021 14:261
20114-Dec-2114-Dec-2312/14/2021 17:161
19913-Dec-2113-Dec-2312/13/2021 17:121
905-Dec-205-Dec-234/5/2021 15:581
2231-Dec-211-Dec-231/6/2022 15:061
1891-Dec-2130-Nov-2312/1/2021 18:041
18629-Nov-2129-Nov-2312/1/2021 17:561
19724-Nov-2124-Nov-2312/2/2021 15:341
18517-Nov-2117-Nov-2312/1/2021 17:561
19316-Nov-2116-Nov-2312/1/2021 21:261
1929-Nov-219-Nov-2312/1/2021 21:251
1969-Nov-219-Nov-2312/2/2021 15:331
1825-Nov-215-Nov-2311/5/2021 18:421
1815-Nov-215-Nov-2311/5/2021 14:491
1912-Nov-212-Nov-2312/1/2021 21:241




4 REPLIES 4
V-lianl-msft
Community Support
Community Support

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
Frequent Visitor

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

Mwoody_05_0-1643675089793.png

 

@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
Super User
Super User

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/

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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