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

[akfir]

Hi everyone,
I have 3 datasets on my model:
1. list of unique customers
2. monthly sales per customer and product
3. calendar table


(1)+(2) connected with [Costomer Code]
(2)+(3) connected with [Date]
 
I wish to create a measure which counts the number of customers bought a product for each 2 rolling months:
 
sample data for your convenience (desired measure result is on the last row of the below).

Currently my measure shows "3" (for my sample above) for each month and does not unify it by month as desired.

Just to mention, i have already created successfully the 2 month rolling sales sum measure.

thanks in advance,
Amit

[Anonymous]

Solution in the file attached.

Best

D

[akfir]

@Anonymous Thanks for your quick and professional reply!

my sales data is monthly, therefore [Date] is always the 1st day for each month.
i focused on your [# Distinct Custs That Bought (2MR)] measure and it seems pretty complex for me and regarding my data.
if i understood correctly, you built some date variables inside the above measure DAX.
i wonder if all these are neccesary due to my "simple" dates values as i mentioned above.
in addition, to correct myself from my original post - i dont have any calendar table because i found it useless as i have date values only on SALES database, so i can use these date values.

if i am right, can you help me with "ligthening" your DAX so it will better fit my project?

thanks in advance,
i appreciate your time,
Amir

[Anonymous]

Hi @akfir.

The fact that you only have months does not exclude using a Calendar and in fact has no bearing on its existence. You have to have a proper Calendar in any decent and professional model regardless of whether you'll use days or not. If you don't want to use days, just hide them. Easy. But having a Calendar enables you to use time-intel functions and this is what you need.

The measure may seem complex (it's not really) because you have to deal with days in the future since a good Calendar always covers whole years and you don't want to show your measures in the future.

I'll post an explanation of the measure shortly.

Best
D

[Anonymous]

# Distinct Custs That Bought (2MR) = 
// This var gets the very latest
// date from the Sales fact table.
var __latestDateInSales =
    CALCULATETABLE(
        LASTDATE( Sales[Date] ),
        ALL( Sales )
    )
// This var gets the latest month
// in which a purchase occured.
// The month identifier must be
// unique across the whole calendar,
// hence I use YearMonthOrder.
var __latestMonthWithSales =
    CALCULATETABLE(
        VALUES( 'Calendar'[YearMonthOrder] ),
        'Calendar'[Date] = __latestDateInSales,
        ALL( 'Calendar' )
    )
// This var gets the very last date
// visible in the calendar in the current
// context.
var __lastDayOfMonth = MAX( 'Calendar'[Date] )
// This variable calculates the period
// of 2 last months starting with the
// currently visible month.
var __periodToCalculateOver =
    DATESINPERIOD(
        'Calendar'[Date],
        __lastDayOfMonth,
        -2, MONTH
    )
// This var just gets you the measure
// over the 2-month period.
var __result =
    CALCULATE(
        [# Distinct Cust That Bought],
        __periodToCalculateOver
    )
// This var is TRUE if the visible period
// of time in the current context is just
// one month.
var __oneFullMonthVisible = HASONEVALUE( 'Calendar'[Year Month] )
// This var makes sure that the curently
// visible month is not after
// the last month in the Sales fact table.
// If it were, it would mean we're trying
// to calculate for the future.
var __periodFullyInThePast =
    MAX( 'Calendar'[YearMonthOrder] ) <= __latestMonthWithSales
// This var is TRUE if the period we're calculating
// in is not in the future and only one month
// is visible in the current context.
var __shouldCalc =
    __periodFullyInThePast
    && __oneFullMonthVisible
return
	// We return the value if all the
	// above relevant conditions are met.
    if( __shouldCalc, __result )

This is the kind of calculation you have to do when you want to have a well-behaved measure that does not show values for the future. The correct calendar in a model is a MUST and it has to cover all full years.

Best

D

[akfir]

Hi @Anonymous 
thanks again for your assistance.
i implemented all your measures including calendar in my project and still do not get the desired solution. 
below attached some snapshots from my tables (exactly same features as you did) and from my data relationships so you can "feel" the data i work with - sorry for not publishing my PBI project. i guess you can understand the sensitivity.
i hope you will manage to further help me out with what you now have.

please consider that eventually i deal with bunch of customers ([cluster]) and need to be able to show the numbers of customers bought product ([SKU]) "x" among all active customers in the same group. this will represent me the availability rate of a group (cluster) for each product (sku) seperately.
for example "cluster 111" has 100 active customers (from CUSTOMERS dataset) and in the last 2 months only 50 customers among the 100 have bought "sku x".  therefore, the availability rate of "cluster 111" for "sku x" is 50%. 3 months ago this rate could have been 30%...

hope you understand my final destination and purpose of the project.

[Anonymous]

I don't understand. If you had implemented the model correctly, the measures I wrote would work OK. Is your Calendar marked as the date table in the model?

By the way, you do not say what specifically is not working, so I can't be of much help.

Best
D

[akfir]

@Anonymous 
yes i did mark it as a date table.

that why i added 2 snapshots so you can see what is wrong: 
please notice that the mid table which presents the [# Distinct Custs That Bought (2MR)] shows the same number for each month. i expect it to show different values.
please have a look at the example of 1 customer with a specific product: right table with sales is ok. but still it shows 1 for each month at the left table (2020-Mar & 2020-Apr should show blank according to your project)

[Anonymous]

Sales of -1? It's more than likely that your data does not adhere to the assumptions that my model works on. Hence you get something you don't expect.

What's a sale of -1? My model works perfectly well and gives correct answers as you've probably seen. If yours does not, assumptions are violated for certain. Well, you did not expose everything important about your model when you were posting this problem...

Best
D

[akfir]

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

[Anonymous]

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

[akfir]

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

[Anonymous]

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

[akfir]

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

[Anonymous · Accepted solution]

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

[akfir]

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

[Anonymous]

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

[akfir]

@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.

[Anonymous]

If you are not getting values for March and April, then it means you've got a problem somewhere in your model. The measures work correctly if all the requirements under which I wrote them are satisfied.

Best
D

[Anonymous]

And since I can't see your data and your model... I can't really help you now.

Best
D

[akfir]

can i send you my project privately?