How to calculate dates using start and end date distribute per month

[beginner]

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

[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. 

[ronrsnfld]

Not enough information for me to even hazard a guess. I suggest you start a new question based on what you have accomplished so far, and ask for suggestions. Show examples of the transformations you require.

[beginner]

Ok, thanks much!

[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 

[ronrsnfld]

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?

[beginner]

My 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 · Accepted solution]

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:

[beginner]

This is lifesaver. Thanks a lot! Your rock. 

[ronrsnfld]

Another undefined scenario is what to happen if there are overlapping dates for different rows of the same incident. As written, a single date will be counted as multiple lost work days if it occurs on separate rows of the same location.

[beginner]

@ronrsnfld @You mean below will not work? There will be one incident per mm/dd/yyyy.  I was not sure what you meant by overlapping days. Do you mind explaining with a simple example?  I am always counting previous month so I think today's date will be always fine.  Thanks

1	1/2/2022	1/3/2022	New York
2	1/2/2022	1/4/2022	New York

[ronrsnfld]

---

1	1/2/2022	1/3/2022	New York
2	1/2/2022	1/4/2022	New York

By overlapping days I mean for the same incident and location. In other words, if those two lines referred to the SAME incident, then 1/2/2022 and 1/3/2022 would be overlapping. If that cannot happen in your data set, then there is no issue.

Since your two lines refer to DIFFERENT incidents, then the count of five (5) lost workdays for NY and Jan-2022 is correct.

[beginner]

@ronrsnfld Hi! Is it all possible to identify distrubition of lost work days per incident for the total number of lost work days? I had doubts since logic was grouping lost work days per month. I have tried to keep incident IDs but it didn't work. 

[ronrsnfld]

Try changing the first line of the  #"Group Rows1" step to the below to include the IncidentID in the Grouping

#"Grouped Rows1" = Table.Group(#"Month To Name", {"Location", "IncidentID"}, {

[beginner]

Yes, it works perfectly! Thanks Just tried to add some other columns such as date but it is throwing a type type or dizziness error 

[ronrsnfld]

I don't understand what you wrote. I don't know what a "dizziness" error is. Perhaps it is time to post a new question starting with what you've been able to accomplish and where you are now having problems. Let me know.

[beginner]

Thank you very much

[ronrsnfld]

Not at my computer to test but maybe also group by incident