Count of months between two dates & assign number to month

[Manny]

Hi team,
I have the attached excel file with test data that I would like to eventually import into power bi or power query using the following columns:
Column A (Initial Test Date) will never be blank
Column B (Passed Y/N?) will always say "No"
Column C (Recent Date) will never be blank
Column D (Status) will either be "Re-testing" or "Passed"

If
I am trying to count the number of months between the two dates (Initial Test Date & Recent Date), and for each of the months
between those two dates to enter a count of 1 only if the following conditions apply:
1. For example on row 2, if Status = Re-testing, then I want to enter a 1 for the month of April and 1 for the month of May (current month) because we
have to count the month from the "Initial Test Date" column and since it did not pass, we have to also include a 1 for the current month; see results K2:L10

2. On row 11, the status is "Re-testing", so we need to enter a 1 for each of the months from Jan thru May
3. same rule applies as row 11, except we now have to also enter a 1 for the month of Dec of the previous year
4. On row 15, since the status is "Passed", the Initial Test Date is in Apr, and it passed testing also in Apr, then we only need to enter a 0 in the month of Apr
5. On row 16, since the status is "Passed", the Initial Test Date is in Mar, and it passed testing in the month of Apr, then we need to enter a 1 for the month of Mar and 0 for the month of Apr

If the status is "Passed", then we do not need to count the month in which "Recent Date" falls on
Also, Column A (Initial Test Date) and Column C (Recent Date) could go back as far as Jan 2006, so I would like to take that into consideration, but only provided a short dataset for simplicity

Thank you so much for your help in advance! Hope this is feasible. If it cannot be done in either power query or power bi, what would be the solution in excel

 

[GraH - Guido]

Hi,
See if this first try helps. For sure this can be optimized, but I do not have the time for it right now.

let
    Source = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Initial Test Date", type datetime}, {"Passed Y/N?", type text}, {"Recent Date", type datetime}, {"Status", type text}}),
    Add_table = Table.AddColumn(#"Changed Type", "Table", each
        let
            Rec_dte_mth = if [Status] = "Passed" then Date.Month([Recent Date]) else null,
            Dates = List.Dates(Date.From([Initial Test Date]), Number.From(Date.From([Recent Date]))-Number.From(Date.From([Initial Test Date]))+1, #duration(1,0,0,0)),
            Months = List.Distinct(List.Transform(Dates, each Date.MonthName(_) )),
            Mths_nr = List.Distinct(List.Transform(Dates, each Date.Month(_) )),
            Count= List.Transform(Mths_nr, each if _ = Rec_dte_mth then "0" else "1"),
            List= List.Transform(Months, each _ & ";" & Count{List.PositionOf(Months, _)})
        in
            Table.PromoteHeaders(Table.Transpose(Table.FromList(List, Splitter.SplitTextByDelimiter(";"), {"Month", "Count"}, null, ExtraValues.Ignore)))),
    Expand_Table = Table.ExpandTableColumn(Add_table, "Table", {"January", "February", "March","April", "May", "June", "July", "August", "September", "October", "November",  "December"}, {"January", "February", "March","April", "May", "June", "July", "August", "September", "October", "November",  "December"}),
    Set_types = Table.TransformColumnTypes(Expand_Table,{{"December", Int64.Type}, {"November", Int64.Type}, {"October", Int64.Type}, {"September", Int64.Type}, {"August", Int64.Type}, {"July", Int64.Type}, {"June", Int64.Type}, {"May", Int64.Type}, {"April", Int64.Type}, {"March", Int64.Type}, {"February", Int64.Type}, {"January", Int64.Type}})
in
    Set_types

 

[p45cal]

The attached is work in progress and needs lots of testing/checking, because, to be truthful, the result is more by luck than design; I was experimenting with stuff and it just seemed to come right.

 

[Manny]

Hi,
See if this first try helps. For sure this can be optimized, but I do not have the time for it right now.

let
    Source = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Initial Test Date", type datetime}, {"Passed Y/N?", type text}, {"Recent Date", type datetime}, {"Status", type text}}),
    Add_table = Table.AddColumn(#"Changed Type", "Table", each
        let
            Rec_dte_mth = if [Status] = "Passed" then Date.Month([Recent Date]) else null,
            Dates = List.Dates(Date.From([Initial Test Date]), Number.From(Date.From([Recent Date]))-Number.From(Date.From([Initial Test Date]))+1, #duration(1,0,0,0)),
            Months = List.Distinct(List.Transform(Dates, each Date.MonthName(_) )),
            Mths_nr = List.Distinct(List.Transform(Dates, each Date.Month(_) )),
            Count= List.Transform(Mths_nr, each if _ = Rec_dte_mth then "0" else "1"),
            List= List.Transform(Months, each _ & ";" & Count{List.PositionOf(Months, _)})
        in
            Table.PromoteHeaders(Table.Transpose(Table.FromList(List, Splitter.SplitTextByDelimiter(";"), {"Month", "Count"}, null, ExtraValues.Ignore)))),
    Expand_Table = Table.ExpandTableColumn(Add_table, "Table", {"January", "February", "March","April", "May", "June", "July", "August", "September", "October", "November",  "December"}, {"January", "February", "March","April", "May", "June", "July", "August", "September", "October", "November",  "December"}),
    Set_types = Table.TransformColumnTypes(Expand_Table,{{"December", Int64.Type}, {"November", Int64.Type}, {"October", Int64.Type}, {"September", Int64.Type}, {"August", Int64.Type}, {"July", Int64.Type}, {"June", Int64.Type}, {"May", Int64.Type}, {"April", Int64.Type}, {"March", Int64.Type}, {"February", Int64.Type}, {"January", Int64.Type}})
in
    Set_types

Thank you so much for your help. I keep getting a "Token ',' expected" error on first "in" statement shown in red text above. Can you help pleaese?

 

[Manny]

Hi Team,
I forgot to mention that I would like to have 2 columns fo the results, one is the Month and the Other is the count per month; I will then need to create a line chart to show a month over month trend.
Thank you for your help!