Last active
January 23, 2018 03:40
-
-
Save piers7/fffc41734da16f5dc12a539a8140199b to your computer and use it in GitHub Desktop.
Power Query / Power BI function to prorata / explode / proportionally allocate data over calendar days
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
let | |
Source = (StartDate as datetime, EndDate as datetime, Value as number) => | |
let | |
OverallDuration = EndDate - StartDate, | |
PerMinuteAllocation = Value / Duration.TotalMinutes(OverallDuration), | |
OneDay = #duration(1,0,0,0), | |
// Date that the overall window starts | |
d0 = DateTime.From(Date.From(StartDate)), | |
d1 = d0 + OneDay, | |
// Date that the window ends (including any tail component - ie dn >= EndDate) | |
dn = if DateTime.Time(EndDate) = #time(0,0,0) then DateTime.From(Date.From(EndDate)) else DateTime.From(Date.From(EndDate) + OneDay), | |
#"dn-1" = dn - OneDay, | |
// Work out total number of calendar days we span over | |
TotalCalendarDays = Duration.TotalDays(dn - d0), | |
// Work out minutes to be allocated in first/last days | |
// Think this approach is clearer than using List.Min() | |
DurationFirstDay = if TotalCalendarDays = 1 then OverallDuration else (d1 - StartDate), | |
DurationLastDay = if TotalCalendarDays = 1 then Duration.From(0) else (EndDate - #"dn-1"), | |
DaysInbetween = List.DateTimes(d1, List.Max({TotalCalendarDays-2,0}), OneDay), | |
DaysInbetweenItems = List.Transform(DaysInbetween, (x) => { x, x + OneDay, OneDay, 1440 * PerMinuteAllocation }), | |
FirstDayItems = { { StartDate, List.Min({d1,EndDate}), DurationFirstDay, Duration.TotalMinutes(DurationFirstDay) * PerMinuteAllocation} }, | |
LastDayItems = if TotalCalendarDays = 1 then {} else { { #"dn-1", EndDate, DurationLastDay, Duration.TotalMinutes(DurationLastDay) * PerMinuteAllocation} }, | |
AllItems = List.Union( { FirstDayItems, DaysInbetweenItems, LastDayItems }), // Flattening projection / select.many / collect / bind | |
Output = Table.FromRows(AllItems, {"From","To","Duration","Allocation"}) | |
in | |
Output | |
in | |
Source |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
If you get 'Operation is not valid for the current state of the object' errors, wrap your Date parameters in DateTime.From(x) (even if they are already apparently dates in your data flow).