This topic contains 74 replies, has 2 voices, and was last updated by 
-
Posts
-
Hi All,
PowerPivot/DAX Newb here, but eager to learn.
I have a data set that tracks monthly maintenance costs for hardware. (Serial # & Monthly Cost.) 3k rows of data or so. I also have the cancellation date of that maintenance. Most are still on maintenance and have no cancellation date, others have been canceled.
I’d like to be able to show the monthly/yearly/quarterly impact of those cancellation requests.
In preparation for this, I’ve added a calendar table to my data model, but it has no relationship to my costs tracking/cancellation sheet.
How should I approach this?
Hello dspblues,
I would like to know a little more about the point-of-view on the maintenance: is your company an entity that provides maintenance to other companies/households or are you a business that has maintenance agreements on equipment that you own?
The answer to the above question is key to working toward the right answer(s) for you.
Your records in the tracking table should also have a date when maintenance was performed (does your table also contain “scheduled” (future) maintenance dates?).
Your calendar table should have one-and-only-one date record for each calendar day for the period of analysis–no gaps or duplicates.
Ideally your calendar table would already have separate columns indicating year, quarter and month for each day.
Hi Tom!
We are receiving maintenance from a few different vendors. These maintenance costs are monthly and cover any maintenance needed. So, I don’t need to care about when maintenance occurs, just that they are under maintenance and have a monthly charge.
Since I have a few different vendors, maintenance cancellation can become effective at different points depending on when the system is decommissioned, but I figured I’d deal with that after I figure this out. Baby steps. 🙂
My calendar table has what you described. I’m using this calendar, but I reduced it to 2014 – 2018 –
http://powerpivotfaq.com/PowerPivot%20Samples/Forms/DispForm.aspx?ID=12
– Warren
Just thinking about this. As these systems are removed from maintenance, I’ll need other values to change. Volume/count, percent of cost, etc.
Warren,
Thanks for the feedback, it helps.
A couple of clarification points for me:
As I picture your tracking table, you have columns for
Serial Number
Monthly Cost
Year (of Cost)
Month (of Cost)
Cancellation Date (date system was decommissioned)You may or may not have other columns, which would could be of interest later on (such a vendor ID), but for now, the columns listed above for your tracking table are important. If monthly costs are not recorded or projected after a cancellation date, Cancellation Date may not be important.
The values you are interested in are listed as:
Volume/count, percent of cost, etc.
Regarding “percent of cost” are you referring to individual measurements for % of monthly, quarterly and annual costs?
Does “count” refer to the number of systems currently under maintenance?
I am not sure what is meant by “Volume” (or maybe volume divided by count is a single metric), if you could help me understand this term better, that would be great.
Looking forward to your response,
Tom
Hi Tom,
I actually don’t have:
Year (of Cost)
Month (of Cost)It’s assumed that if they are on the tracking table w/o a cancellation date, they are on maintenance.
I could put in a “fake” start value, but is that needed?
I do have vendor ID. I have the business unit who is using the system, etc… all valuable data that I hope PowerPivot will help me easily show. Primarily I’m concerned with volume and cost as decommissions happen.
The percent of cost, I thought would be valuable. This way, I can show that cost by vendor, system type, whatever is needed. As time goes on and systems are decommissioned, those values will change.
By volume, I just mean number of systems under maintenance. Counting the number that were canceled in any given month/quarter will be helpful also.
Warren
Warren,
I believe all of your metrics will be easy to resolve once your tracking table is in a format that will have a working relationship with the calendar table.
If I understand your data right, your tracking table contains one record for each piece of hardware, and the monthly amount is for “any” month (other columns do exist, but this would be a meaningful place to start). If so, we may need to reshape it a little to work with the calendar table.
My objective is to have a table that will have a working relationship with the calendar table so Power Pivot can do all of the data crunching for you. I think we are close to a solution.
I will be in a meeting for the next 2 hours, but depending on your answer to the second paragraph I can put together a couple of ideas for a solution in an Excel 2010 or 2013 workbook. So I should probably ask if you have Excel 2010 or Excel 2013? (Either one will be fine, but Excel 2013 offers at least one option that is not in 2010).
Also, is your overall reporting period of analysis (at least to start) something like the last 12 months or current calendar year?
Tom
Hi Tom,
I think you’ve got it. I’m using Excel 2013. My reporting period of analysis is 2015 – 2018. I’ll need monthly views and quarterly views.
Currently I have actual counts and cancellations, but I’ll likely need to add forecasted decommissions and goal amounts.
So, let’s say there’s a 15% reduction goal in volume and finances. I’ll want to be able to track YTD actual + Forecast and compare to the goals.
Did I just make it much more complicated? 🙂
Hello Warren,
Attached is a workbook with some sample data and simple pivots.
A little background before you open the workbook:
Since your tracking table only has one row per system, that will make it a dimension table.
You also need a data (fact) table to work with the calendar data. The data table will have one row for each serial number and for each month that a maintenance agreement is in place. It does not matter how it is made, that can be a complexity that you leave up to your IT people, but I created such a data table with a single Power Pivot formula (using a table that represents hardware information and a list of dates that represent the months covered in the report). In Power Pivot, I named this table “Costs” and the column format is:
SerialNo, Monthly Maintenance Cost, Month.
In order for Month column in the hardware table to relate to the calendar table, it must be a date, so I choose the first day of each month to represent a month.
The calendar table also was marked as the date table inside of Power Pivot (Power Pivot window => Design tab => Mark as Date button).
I added my own calendar table.
The Power Pivot model is ready to go to work, but it only has fictitious data.
At this point, it would be easy to create monthly, quarterly and annual expressions using Power Pivot’s formula language (DAX).
DAX formulas currently in the model include:
Volume:=DISTINCTCOUNT ( Costs[SerialNo] )
Sum of Cost:=SUM ( Costs[MonthlyMaint] )
All Cost:=CALCULATE ( [Sum of Cost], ALL ( Costs ) )
% of Cost:=DIVIDE ( [Sum of Cost], [All Cost] )
Feel free to ask any questions.
Tom
In case you are wondering what the single formula looks like that created the Costs table:
EVALUATE
SUMMARIZE (
FILTER (
CALCULATETABLE ( CROSSJOIN ( Hardware, Months ) ),
ISBLANK ( [CancelDate] )
|| [MonthStart] < [CancelDate]
),
[SerialNo],
[MonthlyMaint],
[MonthStart]
)
ORDER BY [SerialNo], [MonthStart]Wow… Can you expand on how you used a formula to create a new table?
Also, can you explain why you needed to create the data model in this way? I want to fully understand so I know how to do this in the future.
Hello Warren,
Regarding how I created the table, I created the new table from two tables that already had I had already imported into the data model (“Hardware” and “Months”) using a method described when you follow this link:
http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/
The blog that you will read when following the link above describes how to build a linkback table, a feature available in Excel 2013, but not 2010.
One of the cool things about Power Pivot is that, given time, there always seems to be a better way to do something. For example, I woke up this morning and thought: I didn’t need the Months table, I could have created my new table by combining the Hardware table and the Calendar table…
Also, I created the data model in this way after learning how the formula engine works (there are about 6 steps that the formula engine follows over-and-over-again to get results) and how the formula engine uses relationships to lookup and filter data.
I recommend Rob Collie’s book, “DAX Formulas for PowerPivot”. It was a game changer for me because it gave me technical skills to produce while giving me a handle (attitude) regarding the learning curve before me (see Chapter 3, “Learning Power Pivot ‘The Excel Way'”.
Another comment: This example has some advanced components, but they are mostly in the preparation of data. If your employer would approve a few rows of sample hardware/system data with and without cancellation dates (you could change the serial number, vendor ID, etc.), I could make the example more specific to your needs (which, in turn, would prompt more questions). The most important part would be the structure (column names and data types) of the data, not the content.
Also, if you decide to pursue Power Pivot, there is an Excel add-in (free) that you should download from Microsoft: Power Query. Useful when you need to (= often) clean-up and transform data before importing into Power Pivot.
Also, once a month or so, powerpivotpro.com (the host of this forum) offers a free webinar for help those new to Power Pivot and DAX to get started. I recommend this webinar also.
Tom,
Thanks for all the help and the link. I’ll check it out. I have flexibility on this data so I can add columns if needed.
I’ve read Rob’s book, but this is my first attempt trying to use real customer data for something. Also, this scenario wasn’t covered. So, I thought I’d ask for help here.
Based on one of your other posts I did install Power Query and will be checking that out.
Right now, I’m taking Rob’s 22 hr course and reviewing what you sent. I’ll try to create that month lookup table. My employer is a bank… so it would be hard to give you any of that data without basically recreating it. You’ve captured the essence of what I’m trying to do.
After looking at what you provided, I guess my thought was…. “Why can’t I just add a start date of say “1/1/2014” for maintenance to the tracking table? It seemed like that would make your costs table unnecessary. In the tracking table I have SN, monthly cost, etc. The only thing missing was the start date. I think I know why you had to do that, but I can’t quite verbalize it. haha
Hello Warren,
From what I can see, you are taking all the right learning steps.
In Rob’s 22 hour course, there is a lesson near the end of the “warm-up” section entitled, “Thinking Like the Power Pivot Formula Engine” and an accompanying handout, the “Reference Card”. This became the keys for me that opened the door to successfully thinking through and writing DAX formulas (even towards the end of the course when Rob gives lessons on many-to-many relationships).
Regarding the start date, you could add that to your table for hardware, but if you do not have a record of cost for every month, how could Power Pivot give you costs spread out over year(s), quarters and months? What a start date would give you though is the ability to add future or planned systems into you costs table (ooh – “what if” scenarios could be good to offer…)
Besides, if Power Pivot could figure out all of the costs for a system from a single record, wouldn’t we have to find other (less interesting) jobs? 🙂
Tom,
Amazingly enough, I was able to get the costs table created.
So, in this solution, how does the maintenance cancellation dates impact the cost table? It seems like the reason this looks good now is because the cancellation dates were used to create the cost table. No?
Seems like one of the calculate measures should filter out any cost where date is >= cancellation month.
My next challenge will be that the different vendors are calculated differently. One, the change is immediate. Another only allows maintenance updates twice a year. So, this will need to include those calculations.
The forum ‘Power Pivot’ is closed to new topics and replies.