Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
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.
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
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/
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/
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
63 | |
53 | |
39 | |
25 |
User | Count |
---|---|
85 | |
57 | |
45 | |
43 | |
38 |