Determine when credits will run out

[StaceyG]

Hello all you brilliant minds,

I have pre-purchased credits with Fivetran and Snowflake.  I need to write a calculation to determine when the credits will run out based on the run rate.  The .pbix found here contains usage data.  Any suggestions on how to calculate when credits for each system will reach zero?

Thanks!

[v-shex-msft]

Hi @StaceyG,

Did lbendlin 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful one to help others who faced similar requirements to find it more quickly.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[lbendlin · Accepted solution]

Here is the run-out projection

Run Out := 
var d = max('Date'[Date])
var c = CALCULATE(sum('Credit Purchase'[Credits]),REMOVEFILTERS('Date'[Date]))
var di = [CM]-CALCULATE([CM],'Date'[Date]=d-14)
return d+14*divide(c-[cm],di)

CM := 
var d=max('Date'[Date])
return calculate(sum('Usage To Date'[Credits Used]),'Date'[Date]<=d)

[lbendlin]

btw, I corrected your data model. I don't like auto date/time but I let that be your problem, it's not relevant for the discussion.

[lbendlin]

What about the consumption that happened before the first credit date?

[StaceyG]

@lbendlin I supposed it would help to include the credits purchased 🙂  Sorry, stripped that out when I pulled confidential data out of the file.  I've updated the pbix to include that information.

[lbendlin]

@StaceyG  I cannot find the prepaid amount in the data - can you please explain?

By the way - here's a "cheap" option to use the existing Forecast feature 

[StaceyG]

@lbendlin I've uploaded a new file here .  Let me know if that one works for you.  Thanks!

[lbendlin]

Needs more details.  What do you want to base the prediction on - the usage of the total prior period, or some part of it like the last 7 days, or the last month? You seem to have a big change in behavior since mid June?

[StaceyG]

@lbendlin Yes, we have had a big uptick in usage.  I would want to base continued rate on the previous 2 weeks run rate.