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
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 October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
89 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |