Forum Discussion
Count Of Days
hI Rustami4
Try this as a measure:
COUNT OF DAYS = VAR START_DATE_ = CALCULATE ( MIN ( 'Table'[Date In] ), ALLEXCEPT ( 'Table', 'Table'[Well #] ) ) VAR END_DATE_ = CALCULATE ( MAX ( 'Table'[Date Out] ), ALLEXCEPT ( 'Table', 'Table'[Well #] ) ) RETURN DATEDIFF ( START_DATE_, END_DATE_, DAY )START_DATE_ computes for the earliest Date In of a Well #. END_DATE_ computes for the latest Date Out of a Well #
The value being returned is the difference between the two. If you place this measure in a table, you will be seeing the same Count of Days for the same Well # regardless of its dates in and out.
Hi Rustami4,
Are you trying to create something like this:
Well #Start DateEnd DateDifference
| 123 | 4/12/2017 | 4/14/2017 | 2 |
| 123 | 4/14/2017 | 4/17/2017 | 3 |
| 123 | 4/17/2017 | 4/17/2017 | 0 |
| 124 | 4/13/2017 | 4/15/2017 | 2 |
| 124 | 4/15/2017 | 4/18/2017 | 3 |
| 124 | 4/18/2017 | 4/18/2017 | 0 |
| 235 | 3/10/2016 | 3/21/2016 | 11 |
| 235 | 3/21/2016 | 3/30/2016 | 9 |
| 235 | 3/30/2016 | 3/30/2016 | 0 |
| 236 | 3/11/2016 | 3/22/2016 | 11 |
| 236 | 3/22/2016 | 3/31/2016 | 9 |
| 236 | 3/31/2016 | 3/31/2016 | 0 |
If so, I would allow the approach I find simpler and straightforward.
First in Query Editor, I would sort the data by Well # and then by Date columns in ascending order.
Then I would add an index column.
Create a calculated table in DAX using SELECTCOLUMN() function that would select all columns of the original table except that each value in Index column is reduced by one.
Create relationship between the Index columns of the two tables.
Create a calcuated column in the original table using RELATED() function that would return the End date.
Then use DATEDIFF() function to get the difference between the Start and End dates.
Please refer to this PBIX file for details.
https://drive.google.com/open?id=17ILzYtoI7ORtCyAAMPXhuR2uwucZV-RM
You almost get my idea.
I'll try to express differently
well# date IN date OUT
123 03/04/18 03/15/18
.....
123 03/17/18 03/23/18
230 05/19/18 05/27/18
.....
230 05/28/18 06/02/18
Every row in this table is a separate run. Do not pay attention why every well has a few rows, it can probably have a dozen of them.
So..the desired decision for well#123 is number of days between 03/04/18 (the day it's been spudded, initial) and 03/23/18 (the day it's been finished, final day) and futher for every well number in the first column
hI Rustami4
Try this as a measure:
COUNT OF DAYS = VAR START_DATE_ = CALCULATE ( MIN ( 'Table'[Date In] ), ALLEXCEPT ( 'Table', 'Table'[Well #] ) ) VAR END_DATE_ = CALCULATE ( MAX ( 'Table'[Date Out] ), ALLEXCEPT ( 'Table', 'Table'[Well #] ) ) RETURN DATEDIFF ( START_DATE_, END_DATE_, DAY )START_DATE_ computes for the earliest Date In of a Well #. END_DATE_ computes for the latest Date Out of a Well #
The value being returned is the difference between the two. If you place this measure in a table, you will be seeing the same Count of Days for the same Well # regardless of its dates in and out.