Forum Discussion
Power BI: Calculating Product_Code and Days Matching Max Date by Type and Group
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:
- Users will use a slicer to filter the dataset based on the Date range.
- 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:
- Creating the intermediate table (Max(Date) by Type and Group).
- Calculating the number of days matching Max(Date) for each Product_Code.
Any suggestions or DAX solutions would be greatly appreciated!
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.
1 Reply
- rajendraongole1
Super User
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.