dax calculations

[priyankamane]

Hi All,

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?

Thanks

[v-huizhn-msft]

Hi @priyankamane,

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.

Best Regards,
Angelia

[priyankamane]

@v-huizhn-msft

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. 

[v-huizhn-msft]

Hi @priyankamane,

Is there any chance to list your expected result again? So I can provide the solution which is close to your requirement.

Best Regards,
Angelia