Calculate Milage with PowerPivot

**Finless4213** · 05-18-2022, 12:25 PM

I created an Power Pivot sheet for the charging sessions I do with me EV. One column I like to add is the mileage I drove between charging sessions. I write those down some times per month, but always on the last and the first charging session of the month so that a DAX-formula can calculate the difference and it displays the number of kilometres I drove that month. Although this works it isn't perfect. I would rather have an calculation between the first charging session of the month and the first charging session of the next month. Is there any way to accomplish this trough DAX? I added an sheet with example data so you can see what I mean.

Thanks in advance.

**alansidman** · 05-18-2022, 04:09 PM

Using two queries in Power Query/Get and Transform Data and joining them with a left outer join I get the following

Excel 2016 (Windows) 64 bit

	I	J	K
1	Month	Day	Data (2).Mileage
2	1	1	5000
3	2	3	8500
4	3	1	12000
5	4	1	15500
6	5	1	19000
7	6	1	22500
8	7	1	26000

Sheet: Blad1

the Mcodes are in the attached file and can be viewed by clicking on Connections and Queries on the Data Tab.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

**Finless4213** · 05-22-2022, 06:30 AM

This helped quite a lot. Thank you! I have one new question though. Now that I have my monthly mileage, I also want to calculate my power consumption during that same period.
In the example data sheet there is an kW-h column which shows all the kW-h I charged. Is there a simple way to only add up the kW-h during that period so i can divide it my mileage and see my consumption?

**alansidman** · 05-22-2022, 12:16 PM

Here is a consumption calculation for each fill. Hopefully this will work for you.

Excel 2016 (Windows) 64 bit

	I	J
10	Mileage	KWh_Consumption
11	6500	0.033333333
12	7000	0.1
13	8000	0.02
14	8500	0.2
15	10000	0.036666667
16	11500	0.006666667
17	12000	0.1
18	13000	0.085
19	14500	0.003333333
20	15000	0.02
21	15500	0.37
22	18500	0.001666667
23	19000	0.15
24	20000	0.1
25	22000	0.01
26	22500	0.13
27	23000	0.13
28	25500	0.008
29	26000	0.01

Sheet: Blad1

**Finless4213** · 05-23-2022, 11:06 AM

Thanks again for the answer. The problem this way is that it counts every time I enter my mileage. Is it also possible to only count the first time of the month, like with the mileage?

**alansidman** · 05-23-2022, 11:59 AM

If you were to do it manually, mock up what your solution should look like. I want to see if I can replicate what you are looking for. I am not entirely sure of what the calculation should look like. The difference between what I have provided and the mileage is one is a point in time and the other is a cumulative if I am understanding your needs.

**Finless4213** · 05-23-2022, 12:55 PM

I'm sorry that I was unclear. I uploaded an attachment where the calculations are done by hand. With your solution I was able to create the 'Km's driven' column in Power Pivot.
But the 'kW-h charged' column is where I'm stuck at the moment. It needs to add everything from the previous month. After that calculating the consumption should be a breeze.

**alansidman** · 05-23-2022, 04:02 PM

Here is the updated workbook. Several manipulations but you should be able to follow

Excel 2016 (Windows) 64 bit

	M	N	O	P
1	Month	Day	Data (2).Mileage	Merge3.Total KWh.1
2	1	1	4987
3	2	3	8719	200
4	3	5	12567	165
5	4	10	15987	150
6	5	1	18999	190
7	6	2	22789	195
8	7	1	25687	150

Sheet: Blad1

**Finless4213** · 05-25-2022, 01:51 PM

This solved all my problems

Thanks for all the help

!

**alansidman** · 05-25-2022, 02:28 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Calculate Milage with PowerPivot

LinkBack

Thread Tools

Rate This Thread

Display

Calculate Milage with PowerPivot

Re: Calculate Milage with PowerPivot

Re: Calculate Milage with PowerPivot

Re: Calculate Milage with PowerPivot

Re: Calculate Milage with PowerPivot

Re: Calculate Milage with PowerPivot

Re: Calculate Milage with PowerPivot

Re: Calculate Milage with PowerPivot

Re: Calculate Milage with PowerPivot

Re: Calculate Milage with PowerPivot

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] PowerPivot CALCULATE ignoring filter?

[SOLVED] powerpivot Calculate with the two filters

PowerPivot/Dax CALCULATE question

How to calculate discount in PowerPivot??

PowerPivot - how to calculate next month's value?

use excel to calculate Milage

[SOLVED] how do i set up a spreadsheet to calculate fuel milage

Bookmarks

Bookmarks

Posting Permissions