Forum Discussion

chuongnq's avatar
chuongnq
Regular Visitor
1 year ago
Solved

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...
Need Help
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.


     

     

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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.