cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

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. Capture.PNG
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!
5 REPLIES 5
Highlighted
Super User II
Super User II

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

Hi @akfir 

show please your SalesLast3Months statement

 

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


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

Highlighted
Helper II
Helper II

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

SalesLast3Months = CALCULATE([Sales],DATESINPERIOD(Sales[Date],max(Sales[Date]),-3,MONTH))
Highlighted
Super User I
Super User I

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

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper II
Helper II

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

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

 

Highlighted
Super User I
Super User I

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

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.