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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Philipp_L
Regular Visitor

Getting number of days it took to reach target

Hello!

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

Data:

Days in Grace PeriodExited Grace Period subs
1102
2101
376
485
545
656
728
841
914

 

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. 

6 REPLIES 6
fhill
Resident Rockstar
Resident Rockstar

 

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...?

 

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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. 

DateDays in Grace PeriodExited Grace Period subs
01/01/20211102
01/01/20212101
01/01/2021376
01/01/2021485
01/01/2021545
01/01/2021656

Hi @Philipp_L ,

 

My Smaple:

RicoZhou_0-1650520703126.png

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.

RicoZhou_1-1650520726549.png

 

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.

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?

Philipp_L_0-1651695559543.png

 

 

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.

 


 


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. 

Philipp_L_0-1651739139339.png

 




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 🤔

Philipp_L_1-1651739765067.png

 

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



Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors