Calculate Sales of new items that launched in the last 24 months

[jpt1228]

Hello, I have an item dim table with item numbers, invoice table and a date dim table with days, weeks, months etc.. I want to create a calulation that will return the sales of items that have launched in the last 24 months. 

So after the item has sales for 24 months I want them to drop out of the filter.

Thanks

Jon

[v-yuta-msft]

Hi jpt1228,

Based on your description, you want to use present last 24 months sales of each number in Items table, right?

Suppose you have three table:

1. Dim table Items with column Number.
2. Table invoice with column Date, Number and Sales.
3. Dim table Date with column Date.

To achieve your requirement, please refer to steps below:

1. Manage relationship between there table, e.g.: Date and Invoice: one to many, Items and Invoice: one to many.

   

1. Create a measure in Invoice table, use the DAX formula below:

    Sales in last 24 months = CALCULATE (

        SUMX ( Invoice, Invoice[Sales] ),

        DATESBETWEEN ('Date'[Date], EDATE ( TODAY (), -24 ), TODAY () )

    )

The result is like below and you can refer to PBIX file: https://www.dropbox.com/s/ccehjilswvw45o1/For%20jpt1228.pbix?dl=0

Regards,

Jimmy Tao

[jpt1228]

Hello @v-yuta-msft, Thanks for responding. The data tables are as you illustrated. I am not looking to calculate sales of items for the last 24 months but calculate sales for items that do not have sales for 24 months because they are a newly launched item and the calculate sales for items that are not new and have sales history for longer than 24 months.

Once an item has been selling for 24 months I want the item to show up in the Not New Item category.

The reason is becasue we have different KPI's for new items vs existing items such as quality.