Calculate # of Products < 4 years old

mtfrye14

New Member
Joined
Jul 6, 2016
Messages
2
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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):

Code:
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 :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top