Time it will take to reach target

[Anonymous]

Hi all,

I would like to know how to calculate the time it takes for my department to reach its target. For example, if we used the table below, and our target for October is 70, how long will it take for the cumulative column to reach that target?  Based on the previous months, will it take 20 days, 30 days, etc? 

Is there a way to work it out?

Date	Cumulative
12/04/2021	1
28/04/2021	2
30/04/2021	3
30/04/2021	4
30/04/2021	5
07/05/2021	6
10/05/2021	7
13/05/2021	8
14/05/2021	9
14/05/2021	10
17/05/2021	11
25/05/2021	12
09/06/2021	13
01/07/2021	14
08/07/2021	15
08/07/2021	16
22/07/2021	17
26/07/2021	18
26/07/2021	19
26/07/2021	20
06/08/2021	21
09/08/2021	22
25/08/2021	23
26/08/2021	24
26/08/2021	25

Thank you!

[v-xiaotang]

Hi @Anonymous 

Have you solved this problem? If yes, would you mind to accept the answer helpful as the solution or kindly share your solution to improve Power BI.
Really appreciate!

Best Regards,
Community Support Team _Tang

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Is the Cumulative column regular? so what's the regular pattern of the column? On condition that the Cumulative column is incremented by 1 from 26/08/2021 25 at the end of your table, I create a sample.

-

1. Supposing your target value is fixed and set according to month, you need to create a Target table,

2. then create a slicer and put Date column into it, create the measure bellow and put it into Card visual,

Days = 
    var _maxDateinData=MAX(Data[Date])
    var _maxCumulativeinData=MAX(Data[Cumulative])
    var _targetCumulative=SELECTEDVALUE(Target[Target])
    var _diff=_targetCumulative-_maxCumulativeinData
    var _expectedDate=_maxDateinData+_diff
return 
    IF(ISBLANK(_targetCumulative),BLANK(),_diff&"day, "&_expectedDate)

result

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Hi @v-xiaotang , Thank you for this.

This is to forecast when we will reach the amount of users on our system. The date column is for when the users joined the system and the cumulative column is to keep track of how many users we have so far. 

Let's say we have a target of 100 users, can we predict how long it would take to reach that target?

[v-xiaotang]

Hi @Anonymous 

yeah, it can be predicted if you have specific rules, but if you need to predict accurately, you need to use Predict-adjust Model, this involves artificial intelligence.

You can review this article, hope it helps!

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-ai-insights

est Regards,

Community Support Team _Tang

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