Forum Discussion
Helper IHow to calculate dates using start and end date distribute per month
I would like to calculate dates per location and sum the date count per calendar month. When there is no end date I want to keep counting and distrubute it per month.
IncidentID|Lost work start|lost work end|Location
| 1 | 1/2/2022 | 1/3/2022 | New York |
| 1 | 1/5/2022 | null | New York |
| 2 | 2/22/2022 | 2/27/2022 | Tokio |
| 2 | 3/22/2022 | 4/22/2022 | Tokio |
| 2 | 4/25/2022 | 7/2/2022 | Tokio |
desired out come will be
For Incident ID one 1(from first row) + 31(whole month) = 32 lost work days for jan then 28 lost work days for Feb and goes on each month
OK, here is a method using Power Query M Code (which I just happened to develop earlier for a similar problem).
Home =>Trnasform data=>Advanced Editor and paste the code into the window that opens (deleting whatever might be there)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9I3MjAyAjONYUy/1HKFyPyibKVYHZgyU5hcXmlODroSkDjQHLhRQIY5jB2Sn52ZD1dljKTKBImNqgooA7fPXB9dUSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IncidentID = _t, #"Lost work start" = _t, #"lost work end" = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"IncidentID", Int64.Type}, {"Lost work start", type date}, {"lost work end", type date}, {"Location", type text}}), //Create all months list #"Last Day" = Date.From(DateTime.FixedLocalNow()), #"all Dates" = List.Dates( List.Min(#"Changed Type"[Lost work start]), Duration.Days(#"Last Day"-List.Min(#"Changed Type"[Lost work start])) + 1, #duration(1,0,0,0)), #"all MnthYr" = List.Distinct(List.Transform(#"all Dates", each Date.ToText(_,"yyyy-MM"))), //List of Dates for each row #"Days per Month" = Table.AddColumn(#"Changed Type", "Dates List", each let LWD = List.Dates( [Lost work start], Duration.Days(List.Min({[lost work end], #"Last Day"}) - [Lost work start]) + 1, #duration(1,0,0,0)), MnthYr = List.Transform(LWD, each Date.ToText(_,"yyyy-MM")), //Group by MnthYr and count Group = Table.Group(Table.FromColumns({MnthYr} & {LWD}),{"Column1"},{ {"Days in Month", each Table.RowCount(_), Int64.Type}}), Pivot = Table.Pivot(Group,List.Sort(Group[Column1]),"Column1","Days in Month") in Pivot), #"Expanded Dates List" = Table.ExpandTableColumn(#"Days per Month", "Dates List", #"all MnthYr"), #"Sort Month/Year Columns" = let colsToSort = List.RemoveFirstN(Table.ColumnNames(#"Expanded Dates List"),4), #"Sorted Order" = List.Sort(colsToSort), #"Reorder Columns" = Table.ReorderColumns(#"Expanded Dates List",#"Sorted Order") in #"Reorder Columns", #"Month To Name" = Table.RenameColumns(#"Sort Month/Year Columns", List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Sort Month/Year Columns"),4), each{_, Date.ToText(Date.From(_),"MMM-yy")})), #"Grouped Rows1" = Table.Group(#"Month To Name", {"Location"}, { //Sum each month {"Sum Each Month", (t)=> Record.FromList( List.Accumulate(List.RemoveFirstN(Table.ColumnNames(t),4),{}, (a,b)=> a & {List.Sum(Table.Column(t,b))}), List.RemoveFirstN(Table.ColumnNames(t),4)) }}), #"Expanded Sum Each Month" = Table.ExpandRecordColumn(#"Grouped Rows1", "Sum Each Month", List.RemoveFirstN(Table.ColumnNames(#"Month To Name"),4)), #"Typed" = Table.TransformColumnTypes(#"Expanded Sum Each Month", List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Expanded Sum Each Month")), each {_, Int64.Type})) in #"Typed"Results:
23 Replies
ronrsnfldSuper User
1. If there is no end date, how many months to be counted? Some arbitrary date? Today? End of some year? End of time? ???
2. Shouldn't Incident 1 have 2 + 27 days = 29 days in January?
beginnerMy bad. You are right. It should be 29 days fir incident 2. End date should be calculated based on current month - 1 calendar months for last 12 months.
- ronrsnfld
OK, here is a method using Power Query M Code (which I just happened to develop earlier for a similar problem).
Home =>Trnasform data=>Advanced Editor and paste the code into the window that opens (deleting whatever might be there)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9I3MjAyAjONYUy/1HKFyPyibKVYHZgyU5hcXmlODroSkDjQHLhRQIY5jB2Sn52ZD1dljKTKBImNqgooA7fPXB9dUSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IncidentID = _t, #"Lost work start" = _t, #"lost work end" = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"IncidentID", Int64.Type}, {"Lost work start", type date}, {"lost work end", type date}, {"Location", type text}}), //Create all months list #"Last Day" = Date.From(DateTime.FixedLocalNow()), #"all Dates" = List.Dates( List.Min(#"Changed Type"[Lost work start]), Duration.Days(#"Last Day"-List.Min(#"Changed Type"[Lost work start])) + 1, #duration(1,0,0,0)), #"all MnthYr" = List.Distinct(List.Transform(#"all Dates", each Date.ToText(_,"yyyy-MM"))), //List of Dates for each row #"Days per Month" = Table.AddColumn(#"Changed Type", "Dates List", each let LWD = List.Dates( [Lost work start], Duration.Days(List.Min({[lost work end], #"Last Day"}) - [Lost work start]) + 1, #duration(1,0,0,0)), MnthYr = List.Transform(LWD, each Date.ToText(_,"yyyy-MM")), //Group by MnthYr and count Group = Table.Group(Table.FromColumns({MnthYr} & {LWD}),{"Column1"},{ {"Days in Month", each Table.RowCount(_), Int64.Type}}), Pivot = Table.Pivot(Group,List.Sort(Group[Column1]),"Column1","Days in Month") in Pivot), #"Expanded Dates List" = Table.ExpandTableColumn(#"Days per Month", "Dates List", #"all MnthYr"), #"Sort Month/Year Columns" = let colsToSort = List.RemoveFirstN(Table.ColumnNames(#"Expanded Dates List"),4), #"Sorted Order" = List.Sort(colsToSort), #"Reorder Columns" = Table.ReorderColumns(#"Expanded Dates List",#"Sorted Order") in #"Reorder Columns", #"Month To Name" = Table.RenameColumns(#"Sort Month/Year Columns", List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Sort Month/Year Columns"),4), each{_, Date.ToText(Date.From(_),"MMM-yy")})), #"Grouped Rows1" = Table.Group(#"Month To Name", {"Location"}, { //Sum each month {"Sum Each Month", (t)=> Record.FromList( List.Accumulate(List.RemoveFirstN(Table.ColumnNames(t),4),{}, (a,b)=> a & {List.Sum(Table.Column(t,b))}), List.RemoveFirstN(Table.ColumnNames(t),4)) }}), #"Expanded Sum Each Month" = Table.ExpandRecordColumn(#"Grouped Rows1", "Sum Each Month", List.RemoveFirstN(Table.ColumnNames(#"Month To Name"),4)), #"Typed" = Table.TransformColumnTypes(#"Expanded Sum Each Month", List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Expanded Sum Each Month")), each {_, Int64.Type})) in #"Typed"Results:
- Ashish_Mathur
Hi,
Your question is not clear. How have you arrived at the lost days of 32? Please show the expected result in a few more cases.
- beginner
Ronrsnfld answered it already see below
- beginner
ronrsnfld It worked out great. Only problem I had was trying to put them in a line graph for every location for last 12 months. I tried to unpivot columns for each month per location. That has worked for the location which have data since it grouping by location. Some months there is no data and it is skipping. I wanted to show "0" when there is no number for the respecive location for last 12 months. Is it something that can be done by adding a custom column? Any ideas?
- ronrsnfld
Just replace the nulls with zero's as a last step in the Query Editor. I you haven't modified the Query from what I've provided, then we add the #"Replaced Value" step as shown:
#"Typed" = Table.TransformColumnTypes(#"Expanded Sum Each Month", List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Expanded Sum Each Month")), each {_, Int64.Type})), #"Replaced Value" = Table.ReplaceValue(Typed,null,0,Replacer.ReplaceValue, List.RemoveFirstN(Table.ColumnNames(#"Typed"),1) ) in #"Replaced Value"- beginner
ronrsnfld This worked great, thanks again! I have 2 exception I need to consider now. It replaced null dates with zeros which is now in my dataset. Then I changed each month to "datetype" so it will sort by months instead of values( I need to show last 12 calendar months). My incident dataset is joined with location table where there are more locations which never had incidents. I am trying to find a way to populate them into my table and giving them all "0" values even though they have no incidents. Do you have any ideas, how to perform this action? I was thinking to do right join but then I am not sure how to make the month values show 0 in the dataset.
