Products Assortment with sales>0 count in the last 3 months

[akfir]

My data contains a monthly data of products sales. I wish to create a measure that counts the number of products with sales above zero in the last 3 month. 
I already created successfully a measure that sums up the sales in the last 3 months (by using DATESINPERIOD).
i tried this formula: 

Assortment = CALCULATE(count(Sales[product]),filter(sales,[SalesLast3Months]>0),filter(Sales,Sales[Date]=max(Sales[Date])))
but all i get is indeed the number of products with sales>0 but only as of the LAST month and not three aggregated.

appreciate your help in advance!

[az38]

Hi @akfir 

show please your SalesLast3Months statement

do not hesitate to give a kudo to useful posts and mark solutions as solution

[akfir]

SalesLast3Months = CALCULATE([Sales],DATESINPERIOD(Sales[Date],max(Sales[Date]),-3,MONTH))

[JarroVGIT]

I would go with something like as follows. Create a var containing current date, then create a Summary table in a variable per product, with a column "lastthreemonths" using your [SalesLast3Months] measure. Then use COUNTX to filter out all rows with 0 as 'lastthreemonths' and return that value. Something like this (this is without intellisense so probably won't work right away ;))

Measure = 
VAR curDate = MAX(ALLSELECTED(Sales[Date])) //returns 1/1/2019 for January, 1/2/2019 for Feb etc
VAR summaryTable = SUMMARIZE(Sales, Sales[Product], "Last3Months", CALCULATE([Sales], DATESINPERIOD(ALL(Sales[Date]), curDate, -3, MONTH)))
RETURN
COUNTX(FILTER(summaryTable, [Last3Months] > 0), [Product])

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[akfir]

does not work unfortunately ("DatesBetween and DatesInPeriod functions are only accepting date column refernce as a first argument")

i tried this one but it only worked sometimes and now it seems too heavy to load in a table visual:

CALCULATE(distinctcount(Sales[SKU]),filter(sales,[SalesLast3Months]>0),DATESINPERIOD(Sales[Date],LASTDATE(Sales[Date]),-3,MONTH))

is it a lighter way of this?

thanks

[JarroVGIT]

Hmm that makes sense, sorry for not testing my proposed solution. 

Is it possible to share your pbix? That will help a lot in creating the correct DAX for me.  If you want, you can share the link via PM and I will have a look shortly 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂