Calculating number of months that a sale took to reach the target

[Anonymous]

Hi, 

I have the folowing table built:

The info comes from two different tables which are related by the SVID number with a many to many relationship. 

The first seven columns comes from a flat file and the grey part comes from a data source. The column "Installed since close" calculates how mane units have been installed since the Close Month. 

GBS Install since close =

Var Funnel_Close_Month= CONVERT(CALCULATE(MAX('GBS Funnel'[Date]),ALLEXCEPT('GBS Funnel','GBS Funnel'[Close Month])),DATETIME)

Var Mob_Close_Month= CONVERT(CALCULATE(MAX('GBS Mobility'[Date Mobility])),DATETIME)

Var SumGA= CALCULATE(SUM('GBS Mobility'[gross_adds]))

Var Funnel_GA= CALCULATE(MAX('GBS Funnel'[Gross Adds]), ALLEXCEPT('GBS Funnel','GBS Funnel'[Gross Adds]))

Return

IF(CALCULATE(SUMX(FILTER(ALL('GBS Mobility'[Date Mobility]),'GBS Mobility'[Date Mobility]>=Funnel_Close_Month),CALCULATE(SUM('GBS Mobility'[gross_adds]))))>Funnel_GA,Funnel_GA,CALCULATE(SUMX(FILTER(ALL('GBS Mobility'[Date Mobility]),'GBS Mobility'[Date Mobility]>=Funnel_Close_Month),CALCULATE(SUM('GBS Mobility'[gross_adds])))))

Now I need to calculate how many months twere needed to complete all those 9000 units.. So I will expect 13 months as a result (from close month Jan 2020 we sum up the results and until Jan 2021 the 9000 were comppleted)

How can I achieve this?

[Anonymous]

it worked perfectly thanks!!!

[Anonymous]

Thanks, is there a way to share the PBI files and excel tables apart from URL link?. Perhaps an email since I can´t get permissions to share any link.

[Anonymous]

Hi, 

Thats right, so think Jan 2020 as the date the sale was done and Jan 2021 as the date where the units of that sale were completely installed (for exampe if we were selling solar panels). When the cumulative sum of installations reached 9000 we have completed that order. I would like to calculate the time period in months that took to reach those 9,000 orders.  So Jan 2021 is the ending month as you said.

[v-jingzhang]

Hi @Anonymous 

Sorry I'm not sure where I can know Jan 2021 is the month when 9000 were completed. Do we need to cumulate the values from Jan 2020 month by month (48+2786+139+...), until Jan 2021 when the cumulative value reached greater than 9000 the first time, so we think Jan 2021 is the ending month?

Regards,
Community Support Team _ Jing

[Anonymous]

Yes.

Do I need t provide something else?

[v-jingzhang · Accepted solution]

Hi @Anonymous 

I created a simplified model and used a measure to get the number of months. Not sure where you want to show the result so I put it in a card visual. 

Measure = 
VAR __GA = MAX(TableA[GA])
VAR __t = FILTER(TableB,TableB[SVID]=MAX(TableA[SVID])&&TableB[Date]>=MAX(TableA[Sale Date]))
VAR __t2 = SUMMARIZE(__t,[SVID],[YearMonth],[StartOfMonth],"total",SUMX(FILTER(__t,[YearMonth]<=EARLIER([YearMonth])),[Number]))
VAR __endingMonth = MINX(FILTER(__t2,[total]>=__GA),[StartOfMonth])
VAR __closeMonth = MINX(__t,[StartOfMonth])
RETURN
DATEDIFF(__closeMonth,__endingMonth,MONTH)+1

Note that this measure may not be applicable to your model directly because our models are different. You can modify it accordingly to fit your model.

Let me know if you have any questions. If you couldn't get the expected result, share some sample data or sample pbix file after removing sensitive information.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Hi there, interesting..

How would you do it if you were using events instead of months?

Example, i ave this 

Adam is a new employee, and need to know how many more interactions he needs to reach the target score of 4? 

I appreciate any suggestion, Thanks!

[v-jingzhang]

@Anonymous 

You can add an index column to the table to mark the sequence of events. Filter the table to get rows whose accumulative score is greater than or equal to the target. Then get the minimum index from previous filtered result. That row is when he reaches the target.