cancel
Showing results for
Did you mean:
Highlighted
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.
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.

5 REPLIES 5
Highlighted
Super User II

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

Hi @akfir

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

Highlighted
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

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

Proud to be a Super User!

Highlighted
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

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

Proud to be a Super User!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

#### 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

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

Top Solution Authors
Top Kudoed Authors