Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
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 |
Proud to give back to the community!
Thank You!
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
)
)
Proud to give back to the community!
Thank You!
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!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |