Date Difference - Power BI

Question

Based on the below table I want to calculate the difference (in days) between the current date and the previous date this Category appears in the past.

Notes

1. In case there is no previous appearance i do not to show anything.
2. Table in not sorted.

Up to now

I manage to add index in each row ordered by date.

Any ideas how to proceed?

Answer 1

You could try

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"data", each 
    let a=Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index", 0, 1, Int64.Type)
    in Table.AddColumn(a,"Output", each try Duration.Days([Date] - a[Date]{[Index]-1}) otherwise null)
, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date", "Output"}, {"Date", "Output"})
in  #"Expanded data"