Getting number of days it took to reach target

[Philipp_L]

Hello!

I am trying to calculate how many days it took to reach a target based on historical data.

Data:

Days in Grace Period	Exited Grace Period subs
1	102
2	101
3	76
4	85
5	45
6	56
7	28
8	41
9	14

I am trying to calculate how many days 50% of all subs spent in Grace Period. 

I think it should look something like this:
Total_subs = 102 + 101 + 76 + 85 + 45 + 56 + 28 + 41 + 14 = 548

50% Target = 548 / 2 = 274 

So how many days does it takes that first 50% (274) subs to exit Grace Period?
Should it be here some kind of loop?

day_count = 0
1. if 274 - 102 >=  102 then day_count = day_count + 1 else day_count = day_count + (274 / 102 )  
2. if 172 - 101 >= 101 then day_count= day_count + 1 else day_count = day_count + (172 / 101) 
3. if 71 - 76 >= 76 then day_count = day_count + 1 else day_count = day_count + (71 / 76) 
return day_count

And as the result, I can see the total number of days as 2.93 for this example

I hope it makes sense. 

[fhill]

How large of a data set are you going to be working with?  I've figured out a very complex way of expanding your table to the Total Number of Exited Subs, then doing math to get to the 50% mark, but it takes A LOT of overhead.  If you table is small like the sample, should be OK.  If you mocked up something small and we are dealing with hundreds of thousands of actual Exited Subs, it wont work...?

[Philipp_L]

There are thousands of records.
Initial data look like this (data for every day for the last 3 years). The dates I'd like to use as a slicer. 

Date	Days in Grace Period	Exited Grace Period subs
01/01/2021	1	102
01/01/2021	2	101
01/01/2021	3	76
01/01/2021	4	85
01/01/2021	5	45
01/01/2021	6	56

[v-rzhou-msft]

Hi @Philipp_L ,

My Smaple:

Try this code.

Measure = 
VAR _Total_Subs = CALCULATE(SUM('Table'[Exited Grace Period subs]),ALLEXCEPT('Table','Table'[Date]))
VAR _HALF_OF_TOTAL= DIVIDE( _Total_Subs ,2)
VAR _VIRTUAL_TABLE = ADDCOLUMNS(ALL('Table'),"RunningTotal",CALCULATE(SUM('Table'[Exited Grace Period subs]),FILTER(ALLEXCEPT('Table','Table'[Date]),'Table'[Days in Grace Period]<=EARLIER([Days in Grace Period]))))
VAR _MAXDAY = MAXX(FILTER(_VIRTUAL_TABLE,[Date] = MAX('Table'[Date])&&[RunningTotal]<=_HALF_OF_TOTAL),[Days in Grace Period])
VAR _MAXSUM = MAXX(FILTER(_VIRTUAL_TABLE,[Date] = MAX('Table'[Date])&&[RunningTotal]<=_HALF_OF_TOTAL),[RunningTotal])
VAR _SUB_NEXTDAY = SUMX(FILTER(_VIRTUAL_TABLE,[Date] = MAX('Table'[Date])&&[Days in Grace Period] = _MAXDAY+1),[Exited Grace Period subs])
RETURN
IF(_HALF_OF_TOTAL = _MAXSUM, _MAXDAY, _MAXDAY+DIVIDE(_HALF_OF_TOTAL - _MAXSUM ,_SUB_NEXTDAY))

 Result is as below.

Best Regards,
Rico Zhou

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

[Philipp_L]

Hi @v-rzhou-msft

Thank you for your response.
This solution is very close to what I'm looking for, but there are two issues:
1. It only measures within a single day, not a range of days.
2. There is no way to filter by other dimensions. In the table, I have country and product information. Sorry for not mentioning it earlier.

Could you please let me know if it is possible to measure by a range of the dates?

[v-rzhou-msft]

Hi @Philipp_L ,

1. It only measures within a single day, not a range of days.

I think you can change the slicer from list to range to achieve your goal.

2. There is no way to filter by other dimensions. In the table, I have country and product information. Sorry for not mentioning it earlier.

I suggest you to add your dimensions into ALLEXCEPT function.

Measure = 
VAR _Total_Subs = CALCULATE(SUM('Table'[Exited Grace Period subs]),ALLEXCEPT('Table','Table'[Date],'Table'[Country])...)
VAR _HALF_OF_TOTAL= DIVIDE( _Total_Subs ,2)
VAR _VIRTUAL_TABLE = ADDCOLUMNS(ALL('Table'),"RunningTotal",CALCULATE(SUM('Table'[Exited Grace Period subs]),FILTER(ALLEXCEPT('Table','Table'[Date],'Table'[Country]...),'Table'[Days in Grace Period]<=EARLIER([Days in Grace Period]))))
VAR _MAXDAY = MAXX(FILTER(_VIRTUAL_TABLE,[Date] = MAX('Table'[Date])&&[RunningTotal]<=_HALF_OF_TOTAL),[Days in Grace Period])
VAR _MAXSUM = MAXX(FILTER(_VIRTUAL_TABLE,[Date] = MAX('Table'[Date])&&[RunningTotal]<=_HALF_OF_TOTAL),[RunningTotal])
VAR _SUB_NEXTDAY = SUMX(FILTER(_VIRTUAL_TABLE,[Date] = MAX('Table'[Date])&&[Days in Grace Period] = _MAXDAY+1),[Exited Grace Period subs])
RETURN
IF(_HALF_OF_TOTAL = _MAXSUM, _MAXDAY, _MAXDAY+DIVIDE(_HALF_OF_TOTAL - _MAXSUM ,_SUB_NEXTDAY))

Best Regards,
Rico Zhou

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

[Philipp_L]

---

---

Hey @v-rzhou-msft 

1. It only measures within a single day, not a range of days.

I think you can change the slicer from list to range to achieve your goal.

---

Yeah, I tried even with the initial data, but it doesn't work. For single days the measure equals 2.93 and 3.17 but with the range, it is 9, which doesn't make sense. 

---

2. There is no way to filter by other dimensions. In the table, I have country and product information. Sorry for not mentioning it earlier.

I suggest you to add your dimensions into ALLEXCEPT function.

---

I have added as you suggested but it doesn't seem to work properly even for a single day 🤔

Here is the link to the updated power bi file:
Google Drive link