Months Purchased In 12 Months Back

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I am creating a new topic since the previous one was getting confused. I feel like I’ve been running in circles going nowhere :frowning:

O.k this is the situation I have …

After seeing your video “Secondary Table Logic Inside Of Power BI Using DAX” I understand much better the formula you indicated for having the customers grouped by category.

Total Customers by Category = 
CALCULATE( SELECTEDVALUE( 'Customer  Grouping'[Customer Category] , BLANK()),
FILTER( ALL( 'Customer  Grouping'),
[Months Purchased In 12 months back] >= 'Customer  Grouping'[MIN] &&
[Months Purchased In 12 months back] <= 'Customer  Grouping'[MAX] ))

It worked perfect and now, I have the customers grouped by the categories I created. The problem I’m still having is the one I mentioned to you in one of my other previous posts and is related to the formula for the counting of months purchased in 12 months back.

Months Purchased In 12 months back = // Number of months that each particular client purchased in within the last 12 months from the date selected.
VAR MonthlySales = 
CALCULATETABLE(
    SUMMARIZE( 'CALENDAR', 'CALENDAR'[Month & Year], "SalesAmt", [Total Net Sales] ),
        DATESINPERIOD( 'CALENDAR'[Date], MIN( 'CALENDAR'[Date] ), -1, YEAR ) )
RETURN 
COUNTROWS(
    FILTER( MonthlySales, [SalesAmt] > 0 ) )

The idea is having counted the number of months a customer purchased in for the previous 12 months to the period selected but on a monthly basis. In the following example, I’m getting blanks for customers 24 & 25 when I selected the year 2017 when in reality the customer purchased in on that 12 months.

image

image
image.png1449×128 6.08 KB

I’ve been expending almost the whole day trying to find the error, but I wasn’t able to be successful …

I know I’m making you wasting so much time with all my questions, but you’re my last hope …

I really appreciate your help…

Lucia

2

The problem is this I believe

MIN( ‘CALENDAR’[Date] ),

It needs to be MAX instead on min.

What’s happening in that formula and within the Year selected context is that the MIN here is starting from the 1/1/2017, not 31/12/2017 - which sounds like what you likely want here.

This formula might be fine in a monthly context but would fall over in a yearly context as per the example here.

You can see the 24 & 25 didn’t purchase at all in 2016, so it is making sense to me.

So I think that’s likely what you need to change.

The key is understanding the exact context for every single result. I always start there first (as the context is always applied first) then look to work out what each element in the formula is evaluating to based on that context.

See how you go with this.

Sam

1 Like
3

Thank you so much Sam !!!

This has resolved not only my issue with the blanks but also now the sales are matching as well :slight_smile:

image
image.png723×388 115 KB

Chrs,

Lucia

4

Sam,

I’ve been able to create the chart for the total Sales based on the categories

image

but I’m struggling trying to create a similar one for the total count of customers based on the same categories …

image

Could you please help me once again with the DAX formula needed to achieve this based on the supporting table description? :blush:

image

Categories described in the table are based on the frequency of a customer purchasing in 12 months back period.

Lucia

5

Whenever you see something like this the thing to recognize is the Power BI doesn’t know how to filter the results based on the formula you are using.

You likely need to create a formula similar to you other one but use a different measure just after CALCULATE

For example -

= CALCULATE( [Total Customers],
FILTER( …this is where you place exactly the same pattern you have for your sales numbers that are working.

That’s all you need to change.

1 Like
6

Thank you again, Sam!!

I got it …!!!

image

I’m sorry for having seemed so dum working with PBI but I just started few months ago to learn. Now, I’m trying my best to become an expert very soon. I don’t want to keep bothering you this often :slight_smile:

Your tutorials are AMAZING and I have learned a LOT in a very short period of time … Please, keep doing the great job you’re doing educating us in the use of this powerful tool …!!!

Cheers!!

Lucia

7

That’s great.

Just keep working through examples and it will become clearer and clearer as you go. That’s the key.

Chrs
Sam

1 Like