Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Responsive Resident
Responsive Resident

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

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.






Community Support
Community Support

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:





Jimmy Tao

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.



Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.