Calculate # of Products < 4 years old

[mtfrye14]

Hi there,

Just starting to learn DAX and coming across some trickier calculations that I am getting stuck on. I have 4 years of [ProductID], [Date], and [TotalSales]. My products are in my ProductID column. My approach is to use DistinctCount([ProductID]) with the following filters: Sales in 2013 >0, Sales in 2014 >= 0, Sales in 2015 >= 0, Sales in 2016 > 0.

Not sure if I should be using CALCULATE, IF, or FILTER given that I have multiple filters..

Any help would be greatly appreciated.

Thanks

 

[BlueHornet]

Welcome to the board.

Speaking only for myself, it's not at all clear to me what you're after here. It would be helpful if you could post some sample data (it doesn't have to be "real" data, only illustrative) and a view of the results that you hope to achieve. (I also don't know what DAX stands for, and whether it's relevant to the issue at hand; if it is, then perhaps you could at least explain the meaning of the acronym.)

 

[mtfrye14]

So I am looking at historical sales data like below but a much larger data set with multiple product ID's and region. I am ultimately filtering by region to develop a metric that shows by region the "Number of Products <5 years old". Below there are two products, "Road A" and "Road B". in 2011, Road has sales data where Road A does not. In 2012, Road A does have Sales data. I want to capture the products similar to Road A, that are newer. Note, I am trying to capture products where Sales in 2011 = 0, 2012, 2013, 2014, and 2015 >= 0, and 2016 > 0.

Hopefully this clarifies my question, Thanks.

I have been playing around with a number of formulas such as :=CALCULATE(DISTINCTCOUNT([ProductID]), FILTER([TotalSales], [Year]="2011" && [TotalSales]=0, [TotalSales], [Year]="2012" && [TotalSales]>=0, [Year]="2013" && [TotalSales]>=0, [Year]="2014" && [TotalSales]>=0, [Year]="2015" && [TotalSales]>=0, [Year]="2016" && [TotalSales]>0

Year / Region / Category / Product ID / TotalSales
2011 / USA / Bike / RoadA / 0
2011 / USA / Bike / RoadB / $500
2012 / USA / Bike / RoadA / $250
2012 / USA / Bike / Road B / $600
2013 / USA / Bike / RoadA / $400
2013 / USA / Bike / RoadB / 500
2014 / USA / Bike / RoadA / $500
2014 / USA / Bike / RoadB/ $500
2015 / USA / Bike / Road A / $600
2015 / USA / Bike / Road B / $600
2016 / USA / Bike / RoadA / $600
2016 / USA / Bike / RoadB / $600

 

[BlueHornet]

On its face, this seems like the kind of dataset for which we would typically use a Pivot Table to organize the data in a table laid out to show the data you have here in a more meaningful way. Are you familiar with them? They're easier to learn from by playing with them than having them described (at least they were for me), so that's what I would suggest.

Go to "Insert" / "Pivot Table" and select your dataset, then accept the default for "new worksheet" and you're halfway there. Then select the data layout that you want with drag-and-drop fields and you're done. The dataset that you have here is elementary. Pivot tables work great with very large datasets.

 

[Ozeroth]

Hi mtfrye14,


The concept of what you're wanting to measure is similar to a 'new customers' measure, as discussed here:

1. DAX Patterns: New and Returning Customers – DAX Patterns
2. Recent SQLBI article: https://www.sqlbi.com/articles/computing-new-customers-in-dax/

In your case you're wanting to count new products, where "new products" = "products whose first sale occurred <= K years ago".

As a starting point, give this measure a go (I've assumed a single table called Sales):

Number of Products 4 years old or newer
=
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            ADDCOLUMNS (
                VALUES ( Sales[Product ID] ),
                "YearOfFirstSale", CALCULATE ( MIN ( Sales[Year] ) )
            ),
            ALL ( Sales[Year] )
        ),
        CONTAINS (
            FILTER (
                ALL ( Sales[Year] ),
                Sales[Year] <= MAX ( Sales[Year] )
                    && Sales[Year]
                        >= MAX ( Sales[Year] ) - 4
            ),
            Sales[Year], [YearOfFirstSale]
        )
    )
)

This is modelled on SLBI article #2 above.
It counts the number of products that had their first sale between the maximum Year currently filtered and 4 years prior.
It counts products regardless of whether they had a sale in the currently filtered Year(s) - you could modify this.

Owen