L
Legacy 325417
Guest
Hi Everybody,
Firstly, let me preface this enquiry by saying that while I'm proficient using Excel I'm relatively new to PowerPivot/DAX. I would like to set up a persistent model that can automatically calculate the date at which equipment is to be next maintained. Note I have structured this post providing the context firstly followed by my question towards the end of the post.
The query relates to a data set which holds records for a particular type of “Equipment” that is to be maintained. The data is contained within two tables – “Maintenance” and “Details”
I’ve uploaded a clean, stripped down version of my data set to drop box – here:
https://www.dropbox.com/s/9j0m5cejhvxplpp/DAX Operation History_v2.3.xlsx?dl=0
The file preview will look like junk - I suspect because I'm running the 64-Bit Excel version. You will need to download the file to view it - I assure you it is an entirely benign set of numbers.
--- Some Definitions and Context ---
There are two types of maintenance “Activity” – namely: “Inspections” and “Overhauls”
Inspections:
Equipment at a “Site” undergoes monthly "Inspections" at which time the value of a meter that counts the number of "Operations" the equipment has executed is recorded.
Overhauls:
An Overhaul is like a full service that happens typically every couple of years based on information relative to equipment type in the details list, namely:
- Time (months) since last Overhaul – for instance intervals of 24, 48, 12
- Count (# of "Operations") – i.e. inspection reading at last Overhaul
Operations Data:
Interestingly, while the equipment remains unchanged over time, a meter which counts equipment Operations are subject to replacement at which time the counter returns to 0. Therefore taking the maximum of a set of readings for an equipment type does not reflect the true cumulative number of operations at the last date of inspection.
--- Present State ---
The Data Model simply relates the two table by the Equipment Name.
There are two existing measures in use:
- [Last Inspection Date]: the last Inspection Date using the LASTDATE() function
- [Last Overhaul]: based on [Last Inspection Date] when the Activity = “Overhaul”
OK .. Phew! If you’ve made it this far I commend you
--- The Question ---
What I am trying to accomplish is to determine – for each equipment type - the date of the next Overhaul based on either Time or Count of Operations:
Due By Date = Last overhaul date + Interval by Time (Months)
Or
Due By count = Operation Meter Reading at [Last Overhaul] + Interval by Count(Operations)
In other words the equipment is due for overhaul on a set time period unless the last meter reading exceeds the interval between overhauls. Pretty much the way your car is serviced.
Apologies for the granular explanation! This is where your help is needed I want to achieve the above but have fallen short trying to develop a set of measures or calculated columns that will return the key results to
For example - the Count of Operations at the last overhaul. Cannot figure out how to pull this out despite using a myriad of approaches using combinations of values(), calculate(), filter() etc.
I thought originally it would be simple to say something like:
=Calculate(maintenance_history[Operation Meter Reading], maintenance_history=[Last Overhaul])
No such luck. I fear that I’m conflating row calculations with aggregate functions or some such.
At this stage I the best I can do is to write out a procedure as to how *I* think this could be achieved:
Determine the:
- Meter Reading at the date of the last overhaul i.e. [Reading at Last Overhaul]
- Meter Reading at the date of the Last Inspection i.e. [Reading at Last Inspection]
Determine the maximum of the following cases:
- [Reading at Last Overhaul] + Interval by Count(operations)
- [Reading at Last Inspection] + Interval by Count(operations)
I would really appreciate some assistance with this as it is doing my head in and I’ve spent probably 40 hours trying to find a solution to no avail. Annoyingly I can simply look up the data and determine the answer manually in moments however the data set is much larger than that provided in my link therefore precluding manual analysis.
I expect I’ll also want to calculate the date for the next inspection based on a linear forecast of the reading history but this would be a “next steps” level of development. However – if anyone had a suggestion I would gladly welcome their comments.
Many thanks in advance.
My system is running the following:
Excel 2013 64-Bit
PowerQuery
PowerPivot
Firstly, let me preface this enquiry by saying that while I'm proficient using Excel I'm relatively new to PowerPivot/DAX. I would like to set up a persistent model that can automatically calculate the date at which equipment is to be next maintained. Note I have structured this post providing the context firstly followed by my question towards the end of the post.
The query relates to a data set which holds records for a particular type of “Equipment” that is to be maintained. The data is contained within two tables – “Maintenance” and “Details”
I’ve uploaded a clean, stripped down version of my data set to drop box – here:
https://www.dropbox.com/s/9j0m5cejhvxplpp/DAX Operation History_v2.3.xlsx?dl=0
The file preview will look like junk - I suspect because I'm running the 64-Bit Excel version. You will need to download the file to view it - I assure you it is an entirely benign set of numbers.
--- Some Definitions and Context ---
There are two types of maintenance “Activity” – namely: “Inspections” and “Overhauls”
Inspections:
Equipment at a “Site” undergoes monthly "Inspections" at which time the value of a meter that counts the number of "Operations" the equipment has executed is recorded.
Overhauls:
An Overhaul is like a full service that happens typically every couple of years based on information relative to equipment type in the details list, namely:
- Time (months) since last Overhaul – for instance intervals of 24, 48, 12
- Count (# of "Operations") – i.e. inspection reading at last Overhaul
Operations Data:
Interestingly, while the equipment remains unchanged over time, a meter which counts equipment Operations are subject to replacement at which time the counter returns to 0. Therefore taking the maximum of a set of readings for an equipment type does not reflect the true cumulative number of operations at the last date of inspection.
--- Present State ---
The Data Model simply relates the two table by the Equipment Name.
There are two existing measures in use:
- [Last Inspection Date]: the last Inspection Date using the LASTDATE() function
- [Last Overhaul]: based on [Last Inspection Date] when the Activity = “Overhaul”
OK .. Phew! If you’ve made it this far I commend you
--- The Question ---
What I am trying to accomplish is to determine – for each equipment type - the date of the next Overhaul based on either Time or Count of Operations:
Due By Date = Last overhaul date + Interval by Time (Months)
Or
Due By count = Operation Meter Reading at [Last Overhaul] + Interval by Count(Operations)
In other words the equipment is due for overhaul on a set time period unless the last meter reading exceeds the interval between overhauls. Pretty much the way your car is serviced.
Apologies for the granular explanation! This is where your help is needed I want to achieve the above but have fallen short trying to develop a set of measures or calculated columns that will return the key results to
For example - the Count of Operations at the last overhaul. Cannot figure out how to pull this out despite using a myriad of approaches using combinations of values(), calculate(), filter() etc.
I thought originally it would be simple to say something like:
=Calculate(maintenance_history[Operation Meter Reading], maintenance_history=[Last Overhaul])
No such luck. I fear that I’m conflating row calculations with aggregate functions or some such.
At this stage I the best I can do is to write out a procedure as to how *I* think this could be achieved:
Determine the:
- Meter Reading at the date of the last overhaul i.e. [Reading at Last Overhaul]
- Meter Reading at the date of the Last Inspection i.e. [Reading at Last Inspection]
Determine the maximum of the following cases:
- [Reading at Last Overhaul] + Interval by Count(operations)
- [Reading at Last Inspection] + Interval by Count(operations)
I would really appreciate some assistance with this as it is doing my head in and I’ve spent probably 40 hours trying to find a solution to no avail. Annoyingly I can simply look up the data and determine the answer manually in moments however the data set is much larger than that provided in my link therefore precluding manual analysis.
I expect I’ll also want to calculate the date for the next inspection based on a linear forecast of the reading history but this would be a “next steps” level of development. However – if anyone had a suggestion I would gladly welcome their comments.
Many thanks in advance.
My system is running the following:
Excel 2013 64-Bit
PowerQuery
PowerPivot
