Power BI: Calculating Product_Code and Days Matching Max Date by Type and Group

[chuongnq]

I have a dataset A with the following columns:

• Date: Date of the record.
• Product_Code: Product code.
• Type: Product type (e.g., X, Y).
• Group: Product group (e.g., M, N).

Objective:

1. Users will use a slicer to filter the dataset based on the Date range.
2. After the user applies the slicer, the following calculations need to be performed:
   • Identify the Max(Date) for each combination of (Type, Group) within the selected date range.
   • Return the Product_Code corresponding to the Max(Date) for each (Type, Group).

Calculate the number of days matching the Max(Date) for each Product_Code.

Example:

Input (Dataset A - Original Data):

Date Product_Code Type Group

01/01/2024

A

Y

M

02/01/2024

B

Y

M

03/01/2024

C

X

M

04/01/2024

A

X

M

05/01/2024

B

X

N

06/01/2024

C

X

N

07/01/2024

A

Y

N

08/01/2024

B

Y

N

Case 1: User selects Date range (01/01/2024 - 08/01/2024):

Intermediate Table (Max(Date) by Type and Group):

Type Group Max_Date_By_Type_Group Product_Code

Y	M	02/01/2024	B
X	M	04/01/2024	A
X	N	06/01/2024	C
Y	N	08/01/2024	B

Final Result:

Product_Code Amount_Date

A	1
B	2
C	1

---

Case 2: User selects Date range (01/01/2024 - 07/01/2024):

Intermediate Table (Max(Date) by Type and Group):

Type Group Max_Date_By_Type_Group Product_Code

Y	M	02/01/2024	B
X	M	04/01/2024	A
X	N	06/01/2024	C
Y	N	07/01/2024	A

Final Result:

Product_Code Amount_Date

A	2
B	1
C	1

---

Question:

How can I implement this in Power BI? Specifically, I need help with:

1. Creating the intermediate table (Max(Date) by Type and Group).
2. Calculating the number of days matching Max(Date) for each Product_Code.

Any suggestions or DAX solutions would be greatly appreciated!

[rajendraongole1 · Accepted solution]

Hi @chuongnq  - We need to calculate the maximum date for each combination of Type and Group within the selected date range.

create a calculated table for typegroup as below with summarize function : 

MaxDatesByTypeGroup =

ADDCOLUMNS(

    SUMMARIZE(

        FILTER(

            ProdDate,

            ProdDate[Date] >= MIN(ProdDate[Date]) &&

            ProdDate[Date] <= MAX(ProdDate[Date])

        ),

        ProdDate[Type],

        ProdDate[Group]

    ),

    "Max_Date_By_Type_Group", CALCULATE(MAX(ProdDate[Date]))

)

To retrieve the corresponding Product_Code for the calculated Max_Date_By_Type_Group

Product_Code =

CALCULATE(

    MAX(ProdDate[Product_Code]),

    FILTER(

        ProdDate,

        ProdDate[Type] = EARLIER(MaxDatesByTypeGroup[Type]) &&

        ProdDate[Group] = EARLIER(MaxDatesByTypeGroup[Group]) &&

        ProdDate[Date] = EARLIER(MaxDatesByTypeGroup[Max_Date_By_Type_Group])

    )

)

Now, create a measure to calculate the number of days matching Max_Date_By_Type_Group for each Product_Code:

Amount_Date =

SUMX(

    FILTER(

        Proddate,

        Proddate[Product_Code] IN VALUES(MaxDatesByTypeGroup[Product_Code]) &&

        Proddate[Date] IN VALUES(MaxDatesByTypeGroup[Max_Date_By_Type_Group])

    ),

    1

)

output:

Hope this helps.