Age of Incident Week Over Week

[MiguelSantos]

So I have done days of research and I think I just don't fundamentally understand how this is supposed to work. I am fairly new to Power BI but am trying my best. 

Goal:

Get the age of the incidents week over week based on the date they occurred and end either at the current date or at the resolved date. Ultimately would like to create a rolling average of this information.

Sample Data:

Table 1

KEY	Date of incident		Resolved Date
KEY-123	3/11/2022		8/23/2022
KEY-124	4/5/2022

Table 2 - CalendarAuto(1/1/2022 - 12/31/2022)

Columns

- Year

- Month Number
- Month
- Week (mm/dd/yyyy)
- WeekNumber 

Table 3 - Desired Output?? - I add question marks because I dont know if this table is actually needed to perform this sort of calculation

KEY	Date of Incident	End Of Week	Age By Week
KEY-123	3/11/2022	3/13/2022	2
KEY-123	3/14/2022	3/20/2022	9
…Do Until TODAY() Or Resolved Date if not blank
KEY-124	4/5/2022	4/10/2022	5
KEY-124	4/11/2022	4/17/2022	12
…Do Until TODAY() Or Resolved Date if not blank

Relationships
Table1[Date of Incident] : CalendarAuto[Date]

I am open to any feedback and really appreciate any help that I may receive.

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8naN1DU0MlbSUTLWNzTUNzIwMgKyLfSNjCHsWB2YGhOguIm+KUyJUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KEY = _t, #"Date of incident" = _t, #"Resolved Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"KEY", type text}, {"Date of incident", type date}, {"Resolved Date", type date}}),

    #"Expanded Weeks" = Table.CombineColumns(#"Changed Type",
        {"Date of incident", "Resolved Date"},
        each let
            sow = Date.StartOfWeek(_{0},1),
            end = _{1}??Date.From(DateTime.LocalNow()),
            eow = Date.EndOfWeek(end,1),
            n = Date.WeekOfYear(end) - Date.WeekOfYear(_{0}),
            cols = List.Zip(
                List.Accumulate(
                    {1..n},
                    {{_{0}, end}},
                    (s,c) => s & {{Date.AddDays(sow,7*c), Date.AddDays(eow,-7*c)}}
                )
            ),
            tb = Table.FromColumns({cols{0}, List.Reverse(cols{1})}, {"Start","End"})
        in Table.AddColumn(tb, "Age", (r) => Duration.Days(r[End]-_{0})),
        "Week"
    ),
    #"Expanded Expanded" = Table.ExpandTableColumn(#"Expanded Weeks", "Week", {"Start", "End", "Age"})
in
    #"Expanded Expanded"

[MiguelSantos]

So I think this helped a great deal and its getting me close for sure; however, I am getting the following error. When I try to expand the table column it provides.

I tried to go through each line that you sent me to comment on what I believed to be happening after some research and while some of it makes sense, I am unsure of other areas.

let
    Source = Issues,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Key", "Date of Incident", "Resolved", "Problem Type"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date of Incident", type date}, {"Resolved", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Problem Type] = "8D")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Resolved", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"",Replacer.ReplaceValue,{"Resolved"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Resolved", type date}}),
    #"Expanded Weeks" = Table.CombineColumns(#"Changed Type2",
            {"Date of Incident", "Resolved"}, // for each?
            each let
                //Get start of week from Date of Incident
                sow = Date.StartOfWeek(_{0},1),
                //Get final end date by the value of "Resolved" if not Null otherwise get CurrentDate
                end = _{1}??Date.From(DateTime.LocalNow()),
                //Get end of week from "end" value
                eow = Date.EndOfWeek(end,1),
                //Returns last int of range in "List.Accumulate"
                n = Date.WeekOfYear(end) - Date.WeekOfYear(_{0}),
                //Collects lists in a list
                cols = List.Zip(
                    //Not sure I understand what this is doing.
                    List.Accumulate(
                        {1..n},
                        // Date of Incident to end date -- This this a new column?
                        {{_{0}, end}},
                        // Im at a loss here
                        (s,c) => s & {{Date.AddDays(sow,7*c), Date.AddDays(eow,-7*c)}}
                    )
                ),
                //Creates new Table from columns var populating start and end columns?
                tb = Table.FromColumns({cols{0}, List.Reverse(cols{1})}, {"Start","End"})
                //adding column "age" to new table and calculate duration -- I dont know where (r) is coming from though
            in Table.AddColumn(tb, "Age", (r) => Duration.Days(r[End]-_{0})),
            //Does this name the table?
            "Week"
        ),
        #"Expanded Expanded" = Table.ExpandTableColumn(#"Expanded Weeks", "Week", {"Start", "End", "Age"})
in
    #"Expanded Expanded"

[MiguelSantos]

Figured out what my issue was. The first thing that was contributing to the problem is that I had a relationship that was causing some sort of looping look up that Power BI didnt like. Secondly, there was an issue with comparing a date/time field to a null field. So I populated all null values in the column with

DateTime.LocalNow() 

And that resolved the other error I was having. 

[MiguelSantos]

Thank you so much, I will give this a shot!

[Greg_Deckler]

@MiguelSantos This may give you some ideas. 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[MiguelSantos]

Thank you for the guidance, I will look into this and let you know how it goes.