Forum Discussion
How to model this - date fields in two tables, more complicated than this usually is?
Hi,
Read your post and I think you have trouble only in the following part of your post..
"but I'm not sure how best to get the equivalent pipe length for those dates. There could be changes to pipes at multiple points within the date range so summing lengths could end up double-counting some pipes."
Based on what I understood from your post, is it possible for you to perform the following calculation..
Pipe Count on 1st Jan 2017:
| Pipe 1 | Pipe 2 | Pipe 3 | Pipe 4 | Pipe 5 | Pipe 6 | Pipe 7 | Pipe 8 | Pipe 9 | Pipe 10 |
Replacements in January 2017
| |-----| | |-----| | |-----| | Pipe 11 | |-----| | |-----| | |-----| | |-----| | Pipe 12 | |-----| |
Replacements in February 2017
| |-----| | Pipe 13 | |-----| | |-----| | |-----| | |-----| | Pipe 14 | |-----| | |-----| | |-----| |
Live Pipes on 28th Feb 2017
| Pipe 1 | Pipe 13 | Pipe 3 | Pipe 11 | Pipe 5 | Pipe 6 | Pipe 14 | Pipe 8 | Pipe 12 | Pipe 10 |
Calculation:
If you count the number of live pipes between 01st Jan 2017 to 28th Feb 2017, you will get the result 14 from your pipe dataset.
A = count of live pipes between 01st Jan 2017 to 28th Feb 2017 = 14
B = count of pipes replaced between 01st Jan 2017 to 28th Feb 2017 = 4
C = live pipe count without duplication = A-B = 14-4 = 10
Without duplication, 10 is the number of live pipes between the considered period.
In addition to this, in case if the length has increased by adding - say 2 more pipes, you can add that also, provided you have the data of replacements of pipes and additions of pipes separately.
This is as per my understanding of your post. If I am missing any point, please let me know.
After some playing around, and having read part of The Definitive Guide to DAX, I can now reformulate my question a bit...
Basically what I need to do is create a measure that gets the middle date from the filter context and returns the sum of the pipe lengths on that day, i.e. when the start date for each pipe is less than the middle day and the end date for each pipe is greater than the middle day.
Any ideas how I would do this?
My date table is not related to my pipes dataset because there isn't a single date field for the pipes that corresponds to it. Is this a problem?
- parry2k
Super User
dan_evans_ngn can you provide sample data to get the answer. Also here is a topic which help to get solution quicker.
