Count How many prodcut end warranty in times

[daicaboy]

Dear guys,

I have a Table with product warranty time,  I want to measure How many product still in warranty on specific time with out count the product bought after that time.

Product	Receive Date	EndWarranty Date
A	01/02/2022	01/02/2023
B	15/12/2023	15/12/2024
C	15/05/2023	15/05/2024

and the result i want to measure:

Time	Jan-2022	Feb-2022	....	Jan-2023	...	Jan-2024	Jun-2024
Qty	0	1		2		2	1

Thank you for your time.

[zhqw · Accepted solution]

Hello,

Based on your question, I have simulated two tables: one is the fact table (facttable) and the other is the date table (datetable), corresponding to your fact table and the date table used for evaluation. My approach is to add the facttable to the datetable as a new column, and then compare the Receive Date column and EndWarranty Date column of the facttable with the current row of the datetable. This allows us to obtain the rows that meet the criteria and then count these rows to get the result. If my logic does not match your actual logic, you can adjust it accordingly. Below are the code and example screenshots. I hope this helps!

here is the code:

let
Source = Excel.CurrentWorkbook(),
datetable = Source{[Name="datetable"]}[Content],
change = Table.TransformColumnTypes(datetable,{{"DATE", type date}}),
cus = Table.AddColumn(change, "data", each Table.SelectRows( FactData,(x)=>x[EndWarranty Date]>[DATE] and [DATE]>x[Receive Date])),
cus2 = Table.TransformColumns(cus,{"data",each Table.RowCount(_)}),
rename = Table.RenameColumns(cus2,{{"data", "ProductNum"}}),
changDateFomate = Table.TransformColumns(rename,{"DATE",each Date.ToText(_,[Format="yyyy-MM"] )}),
transpose = Table.Transpose( changDateFomate)
in
transpose

[daicaboy]

@zhqw @dufoq3 Thanks you guys. I'm really your help. It's work perfectly.

[dufoq3 · Accepted solution]

Hi @daicaboy, check this:

Result

v1 Pivot

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLSgtSS1S0lFyBGIDQ30DI30jAyMjZI6xUqwOilInIDY01TeEyiJxTNCVOkNkDUyRlEI4EKUBGfl5qUBhF5CFQCkzmDo4B1WdK0jKSN/AHK4OxgGqiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Product = _t, #"Receive Date" = _t, #"EndWarranty Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Receive Date", type date}, {"EndWarranty Date", type date}}, "sk-SK"),
    Ad_WarrantyMonths = Table.AddColumn(ChangedType, "WarrantyMonths", (x)=> 
        List.Generate(
            ()=> x[Receive Date],
            each _ <= x[EndWarranty Date],
            each Date.AddMonths(_, 1),
            each Date.ToText(_, [Format="MMM-yy", Culture="en-US"])
        ), type list),
    ExpandedWarrantyMonths = Table.ExpandListColumn(Ad_WarrantyMonths, "WarrantyMonths"),
    GroupedRows = Table.Group(ExpandedWarrantyMonths, {"Group", "WarrantyMonths"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Ad_SortHelper = Table.AddColumn(GroupedRows, "SortHelper", each Date.FromText("1-" & [WarrantyMonths], [Format="d-MMM-yy", Culture="en-US"]), type date),
    SortedRows = Table.Sort(Ad_SortHelper,{{"SortHelper", Order.Ascending}}),
    RemovedColumns = Table.RemoveColumns(SortedRows,{"SortHelper"}),
    PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[WarrantyMonths]), "WarrantyMonths", "Count", List.Sum)
in
    PivotedColumn

v2 Group (if you want to avoid Pivoting)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLSgtSS1S0lFyBGIDQ30DI30jAyMjZI6xUqwOilInIDY01TeEyiJxTNCVOkNkDUyRlEI4EKUBGfl5qUBhF5CFQCkzmDo4B1WdK0jKSN/AHK4OxgGqiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Product = _t, #"Receive Date" = _t, #"EndWarranty Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Receive Date", type date}, {"EndWarranty Date", type date}}, "sk-SK"),
    Ad_WarrantyMonths = Table.AddColumn(ChangedType, "WarrantyMonths", (x)=> 
        List.Generate(
            ()=> x[Receive Date],
            each _ <= x[EndWarranty Date],
            each Date.AddMonths(_, 1),
            each Date.ToText(_, [Format="MMM-yy", Culture="en-US"])
        ), type list),
    ExpandedWarrantyMonths = Table.ExpandListColumn(Ad_WarrantyMonths, "WarrantyMonths"),
    GroupedRows = Table.Group(ExpandedWarrantyMonths, {"Group", "WarrantyMonths"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Ad_SortHelper = Table.AddColumn(GroupedRows, "SortHelper", each Date.FromText("1-" & [WarrantyMonths], [Format="d-MMM-yy", Culture="en-US"]), type date),
    SortedRows = Table.Sort(Ad_SortHelper,{{"SortHelper", Order.Ascending}}),
    RemovedColumns = Table.RemoveColumns(SortedRows,{"SortHelper"}),
    GroupedRows2 = Table.Group(RemovedColumns, {"Group"}, {{"All", each 
        [ a = Table.RemoveColumns(_, "Group"),
          b = { {"Group"} & Table.ToColumns(a){0}, {[Group]{0}} & Table.ToColumns(a){1} },
          c = Table.PromoteHeaders(Table.FromRows(b))
        ][c], type table}}),
    CombinedAll = Table.Combine(GroupedRows2[All])
in
    CombinedAll

[zhqw · Accepted solution]

Hello,

Based on your question, I have simulated two tables: one is the fact table (facttable) and the other is the date table (datetable), corresponding to your fact table and the date table used for evaluation. My approach is to add the facttable to the datetable as a new column, and then compare the Receive Date column and EndWarranty Date column of the facttable with the current row of the datetable. This allows us to obtain the rows that meet the criteria and then count these rows to get the result. If my logic does not match your actual logic, you can adjust it accordingly. Below are the code and example screenshots. I hope this helps!

here is the code:

let
Source = Excel.CurrentWorkbook(),
datetable = Source{[Name="datetable"]}[Content],
change = Table.TransformColumnTypes(datetable,{{"DATE", type date}}),
cus = Table.AddColumn(change, "data", each Table.SelectRows( FactData,(x)=>x[EndWarranty Date]>[DATE] and [DATE]>x[Receive Date])),
cus2 = Table.TransformColumns(cus,{"data",each Table.RowCount(_)}),
rename = Table.RenameColumns(cus2,{{"data", "ProductNum"}}),
changDateFomate = Table.TransformColumns(rename,{"DATE",each Date.ToText(_,[Format="yyyy-MM"] )}),
transpose = Table.Transpose( changDateFomate)
in
transpose

[daicaboy]

Thanks for your help but i have another problem. It's my fault for not making clear, It will work with count single product.  If i have a group Like Computer include A, B, C and Phone include D, E i cant do that. So do you have any solution. Thanks for your time a lot.

Ex Result:

Group	01/01
Computer	...
Phone	...

[zhqw]

I forgot the result