Calculate days between dates in rows

[fyk_cz]

Hi

i have a this data:

Date	Odo (km)	Fuel (litres)
05.05.2017	34428	37
28.04.2017	33855	37
19.04.2017	33276	39,9
16.04.2017	32636	19
12.04.2017	32330	39,21
04.04.2017	31736	38
30.03.2017	31089	36

so ... i need compute number of days between rows and traveled distance. For each new fuel refueling stop record i need days from previous stop and traveled km. I'm searching this forum for supporting topics, but I did not find anything valuable.

[magsod · Accepted solution]

Hi,

If you wish to create two new columns for days from previous row and traveled distance you can calculate them like this:

First the column for days from previous date=

var curdate='Table1'[Date]
    return
        CALCULATE(
            DATEDIFF(
                MAX('Table1'[Date]);
                curdate;
                DAY
            );
            FILTER('Table1';
                'Table1'[Date]<curdate
            )
        )

Then you create next column for Distance from previous date=

var curdist=[ODO]
    return
        IF(
            not ISBLANK([Days from previous]);
            CALCULATE(
                curdist-MAX('Table1'[ODO]);
                FILTER(
                    'Table1';
                    Table1[ODO]<curdist
                )
            )
        )

Make sure you substitute the table and column names in the code to make this work in your specific case.

Br,

Magnus

[fhill]

I tried using this same code for my situation, but my dates are in a table with a company value as well.  How can I modify your code to account for an additional Company sorting?  (How do I only count days matching the same company?)

Thank You,

FOrrest

Sample desired result:  (If the first row returns 20, and the last row 0 for Bob's, i'm ok with that too; counting forward instead of backwards - whatever's easier)

Company	Delivery	Days Since Last Delivery
Bob's Trucking	1/5/2017	0
Bob's Trucking	1/25/2017	20
Bob's Trucking	1/27/2017	2
Bob's Trucking	2/10/2017	14
Bob's Trucking	2/17/2017	7
Bob's Trucking	3/1/2017	12
June's Trucking	1/4/2017	0
June's Trucking	1/9/2017	5
June's Trucking	1/11/2017	2
June's Trucking	1/18/2017	7
June's Trucking	1/25/2017	7
June's Trucking	2/1/2017	7
June's Trucking	2/8/2017	7
June's Trucking	2/13/2017	5
June's Trucking	2/15/2017	2
June's Trucking	2/23/2017	8
June's Trucking	2/27/2017	4
June's Trucking	3/1/2017	2

[fhill]

I figured this out!!!  I defined a 2nd VAR and then added a && to the filter logic...

FORREST

DaysApart =
var curdate='Dry Counts'[TransactionDate]
var dc = 'Dry Counts'[DC]
return
CALCULATE(
DATEDIFF(
MAX('Dry Counts'[TransactionDate]),
curdate,
DAY
),
FILTER('Dry Counts',
'Dry Counts'[TransactionDate] < curdate && 'Dry Counts'[DC] = dc
)
)

[fyk_cz]

Oh my friend! That's a solution. Many thanks to you. I added a new columns 'Distance per day' and 'Consumption per 100km' based on Days and Distance columns ... and some visualisations. Excelent support from you!!!