Dax Calculation with logic in power bi data analytics

Need help in Data Analytics Calculations. Currently, I am getting historical data for consumption as follows:

on above data, I am adding custom columns for calculating exact consumption(gallons) in no. of days. like:

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

Solution

What you need to do is relatively complicated, but the summary of my solution is:

Calculate the per-day consumption
Calculate the start and end date of each reading (e.g. the previous reading date plus one day, and the reading date)
Expand your data to have 1 row per day rather than 1-row per reading

You want to do these steps before you load the data into your data model (i.e. in your source system, or as the data is loaded using the Query Editor/Power Query).

Below, I assume you're using the Query Editor/Power Query. However, if you can use your source system, it's often the better choice (since the source system may be a database that is vastly faster than your desktop).

Note that your No. of Days calculation doesn't make sense to me. There are more than 38 days between 24 Jan 2016 and 31 Mar 2016. There are also more than 13 days between 10 Jan and 24 Jan. For this reason, it was difficult to tell whether you wanted a new reading to count on the day the previous reading was taken, or on the next full day. I assume the former. Also note, I've proceeded on the basis that your No. of Days calculation is correct

Calculate the Per Day Consumption

This is the easiest step, given that you have already calculated the Consumption and the No. of Days. Just divide one by the other. In the Query Editor, you can click in the Consumption (gallons) column and select Add Column > Standard > Divide. Under Value, choose Use values in a column and then select the No. of Days column.

Calculate the Start & End Date of Each Reading

The date of the reading is the end date, so you can rename Date to be End Date (since a reading is applied retroactively).

For the start date, in the Query Editor, you will need to add an index column (Add Column > Index Column). You will want to make sure your data is sorted by Meter ID and Date Ascending before doing this. Call the column Index.

Next, Add Column > Custom Column and pull the reading date from the prior row. Call the new column Previous End Date for now.

// A try is necessary because we can't get the previous row if there is no previous row (we'll get an error, which we can handle in the 'otherwise' block)
try 
  if 
    // See if the previous row is for the same Meter ID
    [Meter ID] = #"Reordered Columns"{[Index] - 1}[Meter ID] 
  then 
    // If it is, grab the Reading Date from the previous row
    #"Reordered Columns"{[Index]-1}[End Date] 
  else 
    // If this is the first reading for a meter, calculate the Start Date by subtracting the No. of Days from the End Date
    Date.AddDays([End Date], -[No. of Days])
otherwise
  // If this is the first row in the table, also calculate the Start Date by subtracting the No. of Days from the End Date
  Date.AddDays([End Date], -[No. of Days])

Next, you'll want to add 1 to the Start Date, as we want the reading to apply to the day after the previous reading, not on the day of the previous reading.

Note, if you want the reading date to count in the prior period, subtract 1 from the End Date rather than add 1 to the start date (previous end date).

Expand your data to have 1 row per day

At this point, you should have a Meter ID, Start Date, End Date, and per day consumption column that reflects what you expect (i.e. the per day consumption is correct for the date range).

The final step is to duplicate each row for each date in the date range. There are several solutions to this outlined in this thread (https://community.powerbi.com/t5/Desktop/Convert-date-ranges-into-list-of-dates/td-p/129418), but personally, I recommend the technique (and video) posted by MarcelBeug (https://youtu.be/QSXzhb-EwHM).

You should end up with something more like this (after some removing & renaming of columns):

Finally

Now that you have one row per meter & date, with a per day consumption already calculated, you can build a visual. For example, you could do a column chart with Date on the Axis, and Consumption per Day as the value. By default, Power BI will recognize that Date is a date, and will roll it up by Year-Quarter-Month-Day. Press the little 'x' by Year and Quarter, and you'll have a chart that sums up the per day consumption by month. You can also drill down to individual date.

Further Reading