Forum Discussion

fyk_cz's avatar
fyk_cz
Regular Visitor
9 years ago
Solved

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.20...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
fhill
Resident Rockstar
9 years ago

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
fhill's avatar
fhill
Resident Rockstar
9 years ago

 

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