Data Visualization - How to show sales over a products life?

[davidwc]

Hey everyone,

I am stumped on this one. I would like to show how a product performs(Sales Amount) over its lifetime. 

I have a pretty standard sales data model in star schema:

• 1 fact table that has sales order information including salesorderId , date, salesAmount, productId, customerId.
• 3 dimension table for product, date and customer all linked to the fact table with their associated primary/foreign keys.

I am asked to build a chart that shows product age (in years) on the x-axis and sales amount on the y-axis? We would like this chart to be able to be sliced with various columns in the product and customer table. So it looks like creating a calculated table with the values would not work with the requirements, I am thinking there might be a way to acomplish this with measures and calculated columns in the product table but I am just not sure.

For the product start date that is a calculated column based on the first sales order in the prouct table.

Any help would be greatly appreciated!

Edit: This is essentially the visual that Im trying to replicate, without the stages.

[v-ssriganesh]

Hi @davidwc,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @davidwc,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @davidwc,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @Deku for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[Deku · Accepted solution]

Sales Age = 
Var selectedYear = max( years[year] )
var productDates =
    GENERATEALL(
        VALUES( Products[ProductID] )
        ,var firstSale = CALCULATE( MIN( Sales[salesDate] ) )
        var startDt = firstSale + 365 * selectedYear
        return
        DATESINPERIOD( Dates[Date], startDt, 1, YEAR )
    )
return
CALCULATE(
    SUM( Sales[SalesAmount] )
    ,productDates
)

    

[Deku]

works for me

[davidwc]

Ah ok, I know what you were getting at now. Sorry if my requirements were unclear but I was looking the sales for a product over its lifetime. So you could answer questions like:

• What were the sales for product A when it was 2 years old?
• Did the sales increase or decrease for product A in year 1 vs year 5 of its life?

I think that you going for the current age of products based on todays date and the associated sales for the current age.

[gmsamborn]

Hi @davidwc 

I went at it in a different way.  I added the following 2 columns to the product dimension table:

First Sale = 
    CALCULATE(
        MIN( 'Sales'[OrderDate] ),
        ALL( 'Sales'[OrderDate] )
    )


Years of Sales = 
    DATEDIFF(
        [First Sale],
        TODAY(),
        YEAR
    )

Then you can use [Years of Sales] on your x axis.

Let me know if you have any questions.

[Deku]

You would need to create a disconnected year table that you would use on the x-axis.

Years =

Selectcolumns(

Generate series(0,20,1),

"Year", [value]

)

Then in your measure

Var selectedYear = max(years[year])

Var products = 

Filter(

Values( product[productId]]),

Var firstSale = calculate( min( fact[sale date] ) )

Return

Datediff( firstSale, today(), year) = selectedYear

)

Return

Calculate(

Sum( fact[sales amount] ),

Products

)

[davidwc]

Thanks for the reply! I think you are on to something with the Years table and structure of the measure.

However, I just tried this and it did not work. For testing I just used a table visual and selected the Years[Year] column and the measure. The measure is just provides blank values.