Calculate how many days product run in each production line

amrabousamra — Sat, 16 Jan 2021 11:53:51 GMT

Dear all

I have the following data

Date	Product	Line #
1-Jan-21	Product A	Line 1
2-Jan-21	Product A	Line 1
3-Jan-21	Product A	Line 1
4-Jan-21	Product A	Line 1
5-Jan-21	Product A	Line 3
6-Jan-21	Product A	Line 3
7-Jan-21	Product A	Line 3
8-Jan-21	Product C	Line 1
9-Jan-21	Product C	Line 1
10-Jan-21	Product C	Line 1

How can I calculate the duration with dax like that:

Product	Duration	From	To	Line #
Product A	4	1-Jan-21	4-Jan-21	Line 1
Product A	3	5-Jan-21	7-Jan-21	Line 3
Product C	3	8-Jan-21	10-Jan-21	Line 1

Re: Calculate how many days product run in each production line

Fowmy — Sat, 16 Jan 2021 12:17:36 GMT

amrabousamra

You can easily do it in Power Query as well.

Select Product and Line then Group as follows:

Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtT1SszTNTJU0lEKKMpPKU0uUXAEsn0y81IVDJVidaKVjAiqMCaowoSgClM8KozBKswIqjAnqMICU4UzqjssCaowNMCvJBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, #"Line #" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Product", "Line #"}, {{"Start Date", each List.Min([Date]), type nullable date}, {"End Date", each List.Max([Date]), type nullable date}, {"Days", each Table.RowCount(_), Int64.Type}}) in #"Grouped Rows"

topic Re: Calculate how many days product run in each production line in DAX Commands and Tips

Calculate how many days product run in each production line

Re: Calculate how many days product run in each production line