Getting Correct Item Number

Hey PBCIT ,

I use this DAX statement to create a calculated column inside the report table:

Column = 
var ItemNumber = 'report'[Item Number]
var DateReturned = 'report'[Date Returned]
var previousDate = MAXX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] < DateReturned ) , database[LastDateInvoiced] )
var checkPreVDate = IF( ISBLANK( previousDate ) , DateReturned , previousDate + 1)
var nextDate = MINX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] >= DateReturned ) , database[LastDateInvoiced] )
var _item = MAXX( FILTER( 'database' , 'database'[Item Number] = ItemNumber && ( 'database'[LastDateInvoiced] >= checkPreVDate && database[LastDateInvoiced] <= nextDate ) ) ,[Description] ) 
return
_item

What this statement does (or at least should do) is the following: determine the timeframe when an item was used based on the date returned column in the report table. These dates are stored in the variables checkPreviousDate and nextDate. Then these dates are used to filter for the description.

Based on the sample data you provided the report will look like this:

Please be aware, that this kind of calculation can become slow, depending on the size of the database table and the number of versions, this is due to the fact that SSAS Tabular, the underlying database engine of the Power BI data model does not know the data type sequence.

You may have to consider creating the item/description columns outside of Power BI and use a relational database in between (some kind of data warehouse).

Hopefully, this provides what you are looking for.

Regards,

Tom