Pro rata data over previous days

[KLS]

Hi

I'm looking for a way to split data over previous days. I am given a reading from our electricity meter at variable intervals, so I receive this;

Reading Start Date.    Reading End Date.    Units Used.

1st May to 12th May.     650.

13th May to 17th May.   300.

18th May to 2nd June.   1000.

I need something that will look at the start date of the reading, the end date of the reading and then split it evenly over that number of days. So if it were June 2nd now, I'd expect to see the graph showing a total of 133.33 units so far in June (18th May to 2nd June is 15 days, 1000 units divide by 15 days is a daily average of 66.66 units).

I'm new to Power BI so any help would be appreciated.

Many thanks

[Greg_Deckler]

OK, this is mainly about cleaning up your data. I created an Enter Data query and put in your information minus the "." and I did it in two columns, with the "Units Used" in a column and the rest in another column. Then I used the transformation functions in the query editor to split it all out like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMiwuUfBNrFQoyVcwNCrJALGVdJTMTA2UYnWAssYQIbC0OVza2AAqbYGQNspLUfAqzUsFShsagORjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Reading Start Date" = _t, #"Units Used" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reading Start Date", type text}, {"Units Used", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Reading Start Date",Splitter.SplitTextByDelimiter(" to ", QuoteStyle.Csv),{"Reading Start Date.1", "Reading Start Date.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Reading Start Date.1", type text}, {"Reading Start Date.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Reading Start Date.1", "Reading Start Date"}, {"Reading Start Date.2", "Reading End Date"}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns","Reading Start Date",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Reading Start Date.1", "Reading Start Date.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Reading Start Date.1", type text}, {"Reading Start Date.2", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2","Reading End Date",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Reading End Date.1", "Reading End Date.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Reading End Date.1", type text}, {"Reading End Date.2", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Reading Start Date.1", "Reading Start Day"}, {"Reading Start Date.2", "Reading Start Month"}, {"Reading End Date.1", "Reading End Date Day"}, {"Reading End Date.2", "Reading End Date Month"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","st","",Replacer.ReplaceText,{"Reading Start Day"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","nd","",Replacer.ReplaceText,{"Reading Start Day"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","rd","",Replacer.ReplaceText,{"Reading Start Day"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","th","",Replacer.ReplaceText,{"Reading Start Day"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","st","",Replacer.ReplaceText,{"Reading End Date Day"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","nd","",Replacer.ReplaceText,{"Reading End Date Day"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","rd","",Replacer.ReplaceText,{"Reading End Date Day"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","th","",Replacer.ReplaceText,{"Reading End Date Day"})
in
    #"Replaced Value7"

Then I created a custom column in DAX that is simply "2016" like:

Year = 2016

Now you need a Month table (Enter Data query) like:

Reading End Month Num = RELATED(MonthsEnd[MonthNum])

Month	MonthNum
January	1
February	2
March	3
April	4
May	5
June	6

I actually created 2, MonthsStart and MonthsEnd

Relate your Reading Start Month and Reading End Month columns to the MonthsStart and MonthsEnd columns respectively.

2 more custom columns:

Reading Start Month Num = RELATED(MonthsStart[MonthNum])

And 2 more custom column:

StartDate = DATEVALUE([Reading Start Month Num] & "/" & [Reading Start Day] & "/" & [Year])

EndDate = DATEVALUE([Reading End Month Num] & "/" & [Reading End Date Day] & "/" & [Year])

Now the final two columns:

DaysBetween = DATEDIFF([StartDate],[EndDate],DAY)

Average = [Units Used]/[DaysBetween]

This is by no means elegant, but then again, neither is your source data. 🙂 Basically, I just hacked this together to give you the general gist of what you need to do and a vehicle to get there, there are much better approaches I am sure.

[KLS]

Thank you smoupre, your transformations on cleaning up data were interesting and useful to learn.

Unless I've missed something the data is still not being split over the previous days. I now have an average daily use but this still shows on just the date of the reading and not all the days before that. Is this something that can be done in Power BI? 

Thank you

[ImkeF · Accepted solution]

Hi @KLS,

I'd say that this is what M/the query-editor is made for.  Please find my solution in the last 4 lines of the following code. The first lines are only an optimization of the transformation-steps if your data would actually be badly formatted like they are available now.

If you apply your transformation steps clever, you can harvest some benefits PBI is giving like automatic format-conversion (detection of numbers and dates (without years!!)). Have a look at the video to see how this works.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyLC5R8E2sVCjJVzA0KskAsfUUQMDM1EAPrAKizBgiB1ZnjlBnbICiygKhyigvRcGrNC8VpMrQAKwsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Reading Start Date.    Reading End Date.    Units Used." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reading Start Date.    Reading End Date.    Units Used.", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Reading Start Date.    Reading End Date.    Units Used.", "Column1"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Column1] <> "")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",".","",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","st","",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","th","",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","nd","",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value3","Column1",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),{"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Column1.1",Splitter.SplitTextByDelimiter(" to ", QuoteStyle.Csv),{"Column1.1.1", "Column1.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1.1", type date}, {"Column1.1.2", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Column1.1.1", "From"}, {"Column1.1.2", "To"}, {"Column1.2", "Value"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Daily", each [Value]/(Number.From([To]-[From])+1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Dates", each {Number.From([From])..Number.From([To])}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}})
in
    #"Changed Type3"

[KLS]

That is fantastic - thank you so much!

[Vvelarde]

First create a calculated column

DaysDiff = DATEDIFF(Reading[ReadingStartDate];Reading[ReadingEndDate];DAY)

Next Create a measure

DailyAvg = DIVIDE(Sum(Reading[UnitsUsed]);Sum(Reading[DaysDiff]))

Update: Sorry i dint see the preview post.