How to find the last selling date when the product hasn't been sold in the current month

[Eb50]

Hi everyone,

I'm trying to get the last selling date for the combination of products & articles in a measure. I need the result to be linked to the current row context. I mean that if the product has been sold on May first (01-05) it should return a date prior to this date, and if I look at it on April first (01-04) it should also return a date prior to this date.

My data model is juste made of 4 tables, Sales, Calendar, product & customers. Calendar, Customers and Products linked to sales.

I found a formula that works partially :  

Last_Selling_Date:=

VAR Maxdate = CALCULATE(LASTDATE(Sales[Date]);FILTER(ALLEXCEPT(Sales;Products;Articles);Sales[Date]<MIN(Sales[Date])))

RETURN

Maxdate

My problem now, is that the formula works when the product has been sold to the customer this month, but it doesn't work if there hasn't been any sales. There is the results I obtain with the above formula, I've put red crosses where the formula isn't working.

For product 10 & 11 I know for a fact (by checking my data) that a previous date exits. 

Now I think I understand that the filter function create a row context, and obviously if the row with the product and the article doesn't exist on this particular month, it will not show anything.  

How can I solve this problem ? I thought about generating a table with unique values from the calendar table, the product table, and the customer table and then adding a measure giving me the €/Per unit (measure from the sales table). I thought that it would give me a table with all existing values that I could iterate over next but ... I failed to find a solution to do that (if it's a good solution)

I'm using PowerPivot on Excel.

I hope I was clear enough, thanks for reading me !

[Anonymous]

Hi @Eb50 ,

Based on the description, try to modify the dax formula to the following.

Last_Selling_Date = 
VAR CurrentProductArticle = SELECTEDVALUE(Products[ProductID]) & SELECTEDVALUE(Articles[ArticleID])
VAR MaxDateContext = MAX(Calendar[Date])
VAR RelevantSales = 
    FILTER(
        ALL(Sales),
        Sales[ProductID] & Sales[ArticleID] = CurrentProductArticle
        && Sales[Date] < MaxDateContext
    )
VAR LastSaleDate = MAXX(RelevantSales, Sales[Date])
RETURN
    LastSaleDate

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Eb50 Perhaps try removing the < MIN('Sales'[Date]) ? That's the only thing I see that could be excluding things.