Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
daicaboy
Frequent Visitor

Count How many prodcut end warranty in times

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.

 

ProductReceive DateEndWarranty Date
A01/02/202201/02/2023

B

15/12/202315/12/2024

C

15/05/202315/05/2024

 

and the result i want to measure:

 

TimeJan-2022Feb-2022....Jan-2023...Jan-2024Jun-2024
Qty01 2 21

 

Thank you for your time.

2 ACCEPTED SOLUTIONS
zhqw
Regular Visitor

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!

table.pngquery.png

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

View solution in original post

dufoq3
Super User
Super User

Hi @daicaboy, check this:

 

Result

dufoq3_0-1717248395339.png

 

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

5 REPLIES 5
daicaboy
Frequent Visitor

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

 

dufoq3
Super User
Super User

Hi @daicaboy, check this:

 

Result

dufoq3_0-1717248395339.png

 

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

zhqw
Regular Visitor

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!

table.pngquery.png

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
Frequent Visitor

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:

Group01/01
Computer...
Phone...

 

zhqw
Regular Visitor

I forgot the result
solution.png

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors