How to link a bundle start date to an expiry date

[alfacari]

Hi

I am looking to do some analytics on our mobile business. We unfortunately, do not have great analytics at the moment so I am looking to branch out and provide some for the management team. These would be some pretty basic pieces of information but i am struggling to get there.

I will first explain what I am trying to do and then give you a flavour of what I have available.

I am looking to be able to show the user the number of bundles (by bundle name if required) purchased by month, how many MB, Mins, SMS were allocated to each bundles as well as what percentage of this bundle was used for each allocated resource (MBs, min,sms).

But then I would like to be able to link these to a trend bar or line graph that shows for each of these months, the daily usage for each of these bundles, and to be able to filter the usage when clicking on any of the months the bundles were purchased or by bundle names within those months.

My biggest stumbling block seems to be how I get started on this. I have a “Transaction” or “TDR” tables that presents me with the information broken down into the different entries in the table, namely, the “BUNDLE SUBSCRIPTION” – which is the cost price of the bundle, then “BUNDLE”, which is the part that shows how many units of each of the resources are given (i.e. MB, Mins, SMS), then another transaction called “EXPIRATION”, which shows the date of expiry as well as how many of the units were left when the bundle expired. To complicate matters, a customer can purchase a bundle before the bundle expires, and this simply shows as another “BUNDLE_SUBSCRIPTION” and “BUNDLE” entry.

Example data of transactions for bundles:

I am not sure how I can get all this information into Measures to create the “START DATETIME” of the BUNDLE" and “END DATETIME” of the bundle.

Once I have this, I think I might be able to use this information to bring in the usage information from the three usage tables for MB, Min, SMS.

But any guidance on how I can start on this would be most appreciated.
Please let me know if you require any information regarding my model. But initially this is simply just one table that all the information is sitting in.

Thanks,
Alan

[sam.mckay]

Ok bit to do on this one.

I’ll work through my suggestions one by one here.

First, having a date and time column is mostly useless in Power BI. What you need to do (in the query editor preferably) is to break these into two columns. One for date and one for time.

To me it seems the time really isn’t even that relavent for what you need.

Separating these out is crucial.

Because…then you absolutely need a date table which links to the date columns in this table.

Then what I would do is create a few measures. These would be simple measures, something I would call ‘core measures’

These would be for BUNDLE, BUNDLE SUBSCRIPTION, etc.

These to me sounds like they need to be in separate measures because you’ll want to compare them against each other, based on the ALL1000 code for example.

To do this you just need to create a measure like this.

Initial Measure = SUM( DiffValuesColumn)

Bundle Measure = CALCULATE( [Initial Measure], TransactionKeyColumn = "BUNDLE" )

so on and so forth.

This will get you going.

Now I’m missing how to link up the expiration date somehow. You might need to expand on that for me. Somehow you’ll need to work this out somehow.

I think the key here is to get some inital core calc going.

We also would need to work out what the relationship needs to be between the date table and this table. Is it transaction date or new expiration date?

That is an important questions.

Do you also need to show what is essentially ‘live’ at any point between those two dates?

These are all important things to understand.

See how you go with these changes and then let’s work on one calc at a time. That’s the best way to approach this.

The model and also DAX formula will be very important in getting this all working but sounds very doable.

Chrs
Sam

[alfacari]

Hi Sam

Thanks for the start of this. In terms of the basics, I already have a dates table built, as well as a separate column for the TransactionDate and one for the TransactionTime. I have linked the TransactionDate in the TDR table to the and the DATE field in the DATE TABLE.

I am familiar with building the basic core measures and have created the various measures to show the qty and value of Bundle Subscriptions, the allocated Mins, SMS and MBs for Bundles, etc.

I can get the start information ok. I can calculate how many units of the various resources have been given to the user but I cannot find how many have been used until I understand what the expiry date of the bundle is.

Where do I get the expiry date? Well, in the TDR table, there is a field in the “BUNDLE” record that shows the “NewExpirationDate”. This as a standard is the expiration date of the bundle if the bundle is not renewed before the end of the bundle period. So in this instance, the bundle expiry date is easy, as it is on the same line as the “BUNDLE” transactionkey.

Where this becomes difficult, is where a customer purchases another bundle before the end of the normal bundle “NewExpirationDate”. This manifests itself simply as another “BUNDLE” transactionkey. So there is an “IF” section here that probably needs to be considered. This is the part that I am generally stuck on, because a lot of the bundles are renewed before the end of the bundle period. So this is key to getting this information correct.

I am effectively trying to get the next instance of this Bundle record.

Thanks,
Alan

[sam.mckay]

Have read through this one a few times.

Probably a little too difficult to offer real assistance here without seeing an example and working through options.

Are you able to setup a simple demo that can assist with drilling into a solution for what you require?

Thanks