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

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

-1 is return of an item by the customer. we should consider only sales above zero as a sale.

Highlighted
Anonymous
Not applicable

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

That's what I was saying above.

Since I don't know everything
about your model and the nuances of the data... I'm not able to create the exact measures you want.

-1 was not possible in my model and and each sale in my model is > 0.

Best
D
Highlighted
Helper II
Helper II

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

i appreciate it if you try to keep helping me, but if not thats ok as well of course.

in my original model i created a measure of:

2MonthsRollingSales = CALCULATE(sum(Sales[Sales]),DATESINPERIOD(sales[Date],LASTDATE(sales[Date]),-2,MONTH))
which is working fine. only for the distinct custs 2RM: 
Availability POS = CALCULATE(DISTINCTCOUNT(Sales[Customer Code]),FILTERS('Products'[SKU]),filter(MeasureTable,[2MonthsRollingSales]>0)
it is not working.
 
thanks again,
Amit
Highlighted
Anonymous
Not applicable

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

This

filter(MeasureTable,[2MonthsRollingSales]>0)

does absolutely nothing since MeasureTable is not connected to the model in any way and therefore cannot filter any of the tables. Moreover, filtering the table by the condition above either returns all the rows in the table or no rows depending on the value of the measure in the current context.

I've never in my life used the FILTERS function... Why do you use it? If you go to https://docs.microsoft.com/en-us/dax/filters-function-dax you'll see it has nothing to do with filtering under CALCULATE. It's a function that returns the values that are directly applied as filters to a column. As such these values cannot filter any table the way you're trying to use it.

It's not that I don't want to assist you. I can't do it since the nature of your data has not been fully revealed and my assumptions are therefore not fully correct.

Best
D
Highlighted
Helper II
Helper II

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

i dont think there any other nuances regarding my data. overall my project has basic databases as customers list with all their features (including cluster, territory etc.), monthly customers sales of 42 different products, products list and following your comments - calendar table.
i understand your comment regarding the useless FILTER in CALCULATE. can you think of a solution to add this ROLLING 2 MONTHS SALES SUM>0 condition to the distinct count of the ROLLING 2 MONTHS CUSTOMERS and of course related to DATE so each month the 2 above measures will be changed and not be fixed for all months as i have currently.

 

thanks again

Highlighted
Anonymous
Not applicable

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

I think the file attached has what you wanted. However, please check it thoroughly because it does work according to your description but the output is not always obvious. First, to understand it, please filter the slicer by Product 1 and take a good look at the tables in the report.

 

Best

D

View solution in original post

Highlighted
Helper II
Helper II

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

it is working flawlessly! perfect solution!

Thanks much for your time!

Highlighted
Helper II
Helper II

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

Hi again, 
@Anonymous 
After today's refresh - updated up to yesterday's sales data - i got blank data.
In my opinion that is because refresh time is May while the last sales data available is April 30th - maybe the measures get "corrupted" in this situation.
to approve it i will have to wait for May 4th refresh because first sales data for May will be May 3rd (always 1 day after).

My question is whether you can tell me my assumption is correct or not.

Thanks in advance,
Amit

Highlighted
Anonymous
Not applicable

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

The formulas are perfectly fine. If you start to study them, you'll notice that they will not show values for future months. How is a future month defined? It's the first month in which there are no sales after the last month with non-zero sales. Since you don't have any sales in May, May is still in the future.

Best
D

Highlighted
Helper II
Helper II

Re: rolling monthly distinct count customers with rolling monthly sum of sales larger than zero

@Anonymous 

Thanks for your quick reply.

 

I know the measures will not show values for future months, thats extremely fine.

I just wonder why i get blank values. I would expect to see the latest values of March & April.

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.