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

Problem Statement:

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!