Need help in Data Analytics Calculations.
Currently, I am getting historical data for consumption as follows:
Date Reading(gallons) Meter ID
10 Jan 2016 10 1
24 Jan 2016 110 1
31 Mar 2016 225 1
01 Dec 2016 310 1
on above data, I am adding custom columns for calculating exact consumption(gallons) in no. of days. like:
Date Reading(gallons) Meter ID Consumption(gallons) No. of Days
10 Jan 2016 10 1 10 1
24 Jan 2016 110 1 100 13
31 Mar 2016 225 1 115 38
01 Dec 2016 310 1 35 245
Now, I have to plot month wise bar chart for consumption of respective Meter ID in 2016 year. But problem here is, I will have to calculate Every months consumption by dividing it in days in each respective month of 2016, and then only I will able to plot them monthly like:
y axis = consumption in every month
x axis = Jan Feb March Apr May Jun Jul Aug Sep Oct Nov Dec
so, in jan month, consumption should be = 10 + 100 + ((115/38) * 7) gallons
Notes: here, in ((115/38) * 7) : we are calculating avg consumption of single day 7 days in Jan and whole march and then getting last 7 day consumption of Jan so that we can add it in calculation of total consumption of Jan month
but how to add measure/custom column/new table for these calcualtions?
After long time research, your calculation for Jan makes no sense, there are two reasons. Please share more details and post expected results for further analysis. Thanks for understanding.
1. In your sample table, there are Feb between 24 Jan 2016 and 31 Mar 2016, how to calculate consumption in Feb. If calculate consumption in Feb is 0, why you use the ((115/38) * 7) single day 7 days in Jan?
2. If you calculated the consumption in Feb based on your solution, it should be 28*(115/38), but there are more than 38 days between 24 Jan 2016 and 31 Mar 2016, which is inconsistent.
Sorry, my mistake in no. of days calculation, insted of 38, it should be 66 (with feb month days also) as it is calculated as per thle last reading date to current reading date. this is just an example. but formula will be same to calculate it.