How to get last sales date/sales amount for each product -Analysis Service

How to get last date/last sales amount for each product when product was sold.

We are looking at a sample which will show last sales date and slaes amount for each product. Similarly we can get first date when product was sold using this function. We can use LastNonEmpty function but its not available except developers edition.



We will use GENERATE function which will run for each product and will get last date when product was sold for each product.

We will use AdventureWorks Sample cube for this query.


select [Measures].[Internet Sales Amount] on 0,
generate
([Product].[Product Categories].[Product],
tail(nonempty({[Product].[Product Categories].currentmember*[Date].[Calendar].[Date].members},[Measures].[Internet Sales Amount]),1) )on 1
from
[Adventure Works]
;




To get first date when product was sold

select [Measures].[Internet Sales Amount] on 0,
generate
([Product].[Product Categories].[Product],
head(nonempty({[Product].[Product Categories].currentmember*[Date].[Calendar].[Date].members},[Measures].[Internet Sales Amount]),1) )on 1
from
[Adventure Works]
;