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:

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.

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

4 Replies

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