Count Of Days

[Rustami4]

Guys!

I was seeking a similar topic for this, but couldn't find it.

The point is I have a set of oil wells, each of them has a number that identifies it. Another words a set of numbers... ANd I want to build a plot that can display a number of days that had been spent for every well constructon. There is a set of dates for every well: entry date, some intermediate dates and a final date shown as follows - 13.03.2018 (for example). Is there any chance to calculate a days number based on these dates? So...I have 2 columns of data..1. Well numbers. 2. Dates as Entry date.....Intermediate dates....Final date (end of well cite construction) along the same column. 

HERE IS AN EXAMPLE

#WELL         DATE

  123            12.04.2017

  123            14.04.2017

  123            17.04.2017

  235            10.03.2016

  235            21.03.2016

  235            30.03.2016

and so on..

[danextian]

Hi @Rustami4,

If i understand correctly, all your dates are in one column and there are three dates for each Well #?

[danextian]

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

[Rustami4]

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

[danextian · Accepted solution]

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.

[JoHo_BI]

Hi @Rustami4

Difference = MAX(WellData[Date]) - MIN(WellData[Date])

Hope that helps!

[Rustami4]

this Dax doesn't work in this case unfortunately. it defines the max number of days in a current month (30,31). But I need an overall number of days between two dates (example: from 04/03/18 till 04/12/18 - makes 9 days)