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

Anonymous's avatar
Anonymous
Not applicable
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

  • fyk_cz's avatar
    fyk_cz
    Regular Visitor
    9 years ago

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

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