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

[chuongnq]

1. • 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!

[rohit1991 · Accepted solution]

hi
1) Use the following DAX formula to create a calculated table:

IntermediateTable = 
SUMMARIZE(
    FILTER(
        'Dataset A',
        'Dataset A'[Date] IN VALUES('DateTable'[Date])
    ),
    'Dataset A'[Type],
    'Dataset A'[Group],
    "Max_Date_By_Type_Group", MAX('Dataset A'[Date]),
    "Product_Code",
    FIRSTNONBLANK(
        FILTER(
            'Dataset A',
            'Dataset A'[Date] = MAX('Dataset A'[Date])
        ),
        'Dataset A'[Product_Code]
    )
)

2) Use this DAX measure to calculate the count of days matching Max_Date_By_Type_Group:

Amount_Date = 
CALCULATE(
    COUNTROWS('Dataset A'),
    'Dataset A'[Date] IN DISTINCT(IntermediateTable[Max_Date_By_Type_Group]),
    'Dataset A'[Product_Code] IN DISTINCT(IntermediateTable[Product_Code])
)

Visualize:

• Add Product_Code and Amount_Date to a table visual.
• The slicer on Date will dynamically filter results.

This ensures the correct Product_Code and Amount_Date based on the slicer selection.