cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

Calculate days between dates in rows

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Supplier

Re: Calculate days between dates in rows

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

4 REPLIES 4
Highlighted
Solution Supplier

Re: Calculate days between dates in rows

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

Highlighted
Regular Visitor

Re: Calculate days between dates in rows

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!!!

Highlighted
Solution Sage

Re: Calculate days between dates in rows

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
Highlighted
Solution Sage

Re: Calculate days between dates in rows

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
)
)

Announcements

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors