Revenue from new products

[faaz]

Hi BI Community.

I am having issues creating a measure for "Revenue from new products", which I hope you can help me with. 

The issue is that i want to create a measure that returns the revenue of newly launched products within the last 3 years. The launch date is set when a given product reached 50K in revenue. 

Items are in "Items" table and Date is in "Date" table. 

Thank you very much in advance!

[Bibiano_Geraldo · Accepted solution]

Hi @faaz ,

Create a Calculated Column for Cumulative Revenue In the "Items" table to compute the cumulative revenue for each product over time:

Cumulative Revenue = 
CALCULATE(
    SUM(Items[Revenue]),
    FILTER(
        ALL(Date),
        Date[Date] <= EARLIER(Date[Date]) &&
        Items[Product] = EARLIER(Items[Product])
    )
)

Add another calculated column in the "Items" table to capture the date when the cumulative revenue of a product first exceeds 50K:

Launch Date = 
CALCULATE(
    MIN(Date[Date]),
    FILTER(
        ALL(Date),
        Items[Cumulative Revenue] >= 50000 &&
        Items[Product] = EARLIER(Items[Product])
    )
)

Create a calculated column that evaluates whether the product's launch date falls within the last 3 years based on the current context:

Is New Product = 
IF(
    Items[Launch Date] >= DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY())),
    TRUE,
    FALSE
)

Finally, create the measure to calculate the total revenue for products classified as "new":

Revenue from New Products = 
CALCULATE(
    SUM(Items[Revenue]),
    FILTER(
        Items,
        Items[Is New Product] = TRUE
    )
)

Let me know if you face any issues!

[Anonymous]

Hi, @faaz 

I wish you all the best. Other members of the community have offered several solutions to help you solve the problem. Since these solutions are able to implement your needs, I would like to confirm that you have successfully solved this problem or do you need further help?
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

[Kedar_Pande · Accepted solution]

@faaz 

Create Measures

Launch Date =
CALCULATE(
MIN('Date'[Date]),
FILTER(
'Items',
CALCULATE(SUM('Items'[Revenue])) >= 50000
)
)

Is New Product =
VAR LaunchDate =
CALCULATE(
MIN('Date'[Date]),
FILTER(
'Items',
CALCULATE(SUM('Items'[Revenue])) >= 50000
)
)
VAR TodayDate = TODAY()
VAR IsNew = LaunchDate >= DATE(YEAR(TodayDate) - 3, MONTH(TodayDate), DAY(TodayDate))
RETURN
IF(IsNew, 1, 0)

This measure returns 1 for products launched in the last three years and 0 otherwise.

Revenue from New Products =
CALCULATE(
SUM('Items'[Revenue]),
FILTER(
'Items',
[Is New Product] = 1
)
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Bibiano_Geraldo · Accepted solution]

Hi @faaz ,

Create a Calculated Column for Cumulative Revenue In the "Items" table to compute the cumulative revenue for each product over time:

Cumulative Revenue = 
CALCULATE(
    SUM(Items[Revenue]),
    FILTER(
        ALL(Date),
        Date[Date] <= EARLIER(Date[Date]) &&
        Items[Product] = EARLIER(Items[Product])
    )
)

Add another calculated column in the "Items" table to capture the date when the cumulative revenue of a product first exceeds 50K:

Launch Date = 
CALCULATE(
    MIN(Date[Date]),
    FILTER(
        ALL(Date),
        Items[Cumulative Revenue] >= 50000 &&
        Items[Product] = EARLIER(Items[Product])
    )
)

Create a calculated column that evaluates whether the product's launch date falls within the last 3 years based on the current context:

Is New Product = 
IF(
    Items[Launch Date] >= DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY())),
    TRUE,
    FALSE
)

Finally, create the measure to calculate the total revenue for products classified as "new":

Revenue from New Products = 
CALCULATE(
    SUM(Items[Revenue]),
    FILTER(
        Items,
        Items[Is New Product] = TRUE
    )
)

Let me know if you face any issues!

[grazitti_sapna]

Hi @faaz ,

To calculate "Revenue from New Products" for products launched within the last 3 years, based on the point when they reach 50K in revenue, we can break the problem into a few steps:

1. Identify the products that reached 50K in revenue in the last 3 years.
2. Calculate the revenue for these products.

Step 1: Create a "Launch Date" for each product
First, we need to identify when each product reached 50K in revenue. We can calculate the launch date for each product by identifying the first date when the cumulative revenue for that product exceeds 50K.

This will require a calculated column to store the launch date for each product. You can do this using

LaunchDate = 
CALCULATE(
MIN('Date'[Date]), 
FILTER(
'Sales', 
SUMX(
FILTER(
'Sales', 
'Sales'[ProductID] = EARLIER('Sales'[ProductID]) && 
'Sales'[Date] <= EARLIER('Date'[Date])
),
'Sales'[Revenue]
) >= 50000
)
)

• Explanation: This formula calculates the first date when the cumulative revenue for each product reaches 50K, using SUMX to accumulate the revenue for each product and FILTER to ensure we are considering all sales data up to that point.

Step 2: Create the "Revenue from New Products" Measure
Now, we want to create a measure that will calculate the revenue for products launched in the last 3 years. We'll do this by filtering the products based on their launch date and summing their revenue:

RevenueFromNewProducts = 
CALCULATE(
SUM('Sales'[Revenue]), 
FILTER(
'Sales', 
'Sales'[LaunchDate] >= TODAY() - 365 * 3 && -- Only consider products launched in the last 3 years
'Sales'[LaunchDate] <= TODAY() && -- And products that have already been launched
'Sales'[Revenue] > 0 -- Ensure we are summing the revenue for valid sales
)
)

Explanation:

• SUM('Sales'[Revenue]): Sums the revenue for the products.
• FILTER: We filter for only the products launched within the last 3 years ('Sales'[LaunchDate] >= TODAY() - 365 * 3).
• 'Sales'[Revenue] > 0: Ensures we are summing only the valid revenue.

Step 3: Use the Measure in Visuals
Now you can use the RevenueFromNewProducts measure in your visuals

I hope the provided solution works for you

If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.

[ajohnso2]

Is there any relationship between Item and Date?

Please provide a sample of your data

[faaz]

Hi,

Yes, there is a link between the two

[PijushRoy]

Hi @faaz 

Can you please check below DAX

NewlyLaunchedProductsRevenue = 
VAR CurrentDate = MAX(Date[Date])
VAR ThreeYearsAgo = EDATE(CurrentDate, -36) 
RETURN
    CALCULATE(
        SUM(Items[Revenue]),
        FILTER(
            DateTable, 
            DateTable[Date] >= ThreeYearsAgo && DateTable[Date] <= CurrentDate 
        )
    )