cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Calculate days between dates in rows

Hi

i have a this data:

DateOdo (km)Fuel (litres)
05.05.20173442837
28.04.20173385537
19.04.20173327639,9
16.04.20173263619
12.04.20173233039,21
04.04.20173173638
30.03.20173108936

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

View solution in original post

4 REPLIES 4
Highlighted
Solution Supplier
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

View solution in original post

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

 

CompanyDeliveryDays Since Last Delivery
Bob's Trucking1/5/20170
Bob's Trucking1/25/2017 20
Bob's Trucking1/27/2017 2
Bob's Trucking2/10/2017 14
Bob's Trucking2/17/2017 7
Bob's Trucking3/1/2017 12
June's Trucking1/4/2017 0
June's Trucking1/9/2017 5
June's Trucking1/11/2017 2
June's Trucking1/18/2017 7
June's Trucking1/25/2017 7
June's Trucking2/1/2017 7
June's Trucking2/8/2017 7
June's Trucking2/13/2017 5
June's Trucking2/15/2017 2
June's Trucking2/23/2017 8
June's Trucking2/27/2017 4
June's Trucking3/1/2017 2
Highlighted
Solution Sage
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
)
)

 

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

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

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