Category missing from Sales table summarized by Group

[lbarbeyto]

Hi Fellow Power BI Users -

I’ve been watching your video “Dynamically Group Customers by Ranking”. Following the indications, I created the measure to group the customer categories I created the other day (thanks to another great video of yours) based on their sales. While I was checking the results, I realized that the sporadic category (lost I assume it should be blank) is missing from the table … any thoughts about why this could have happened?

This is the detail about what I’m explainig…

Thanks as always for your support

Lucia

[sam.mckay]

Really the only reason this would be is that there is no results under sporadic.

When there is no values Power BI will just insert BLANK and when the results is blank a table won’t show it.

You can show this though if you need to.

It the inputs sections go to this drop down

Then turn this on

See how you go with this.

Sam

[lbarbeyto]

That’s not the problem. I’ve been reviewing all the formulas and results and I found out that many fields in my Last Purchase Date are blank when those customers have purchased in the period selected.

I used the following formula for the measure and it worked for some but not for all the customers

Last Purchase Date = 
CALCULATE( MAX( 'SALES '[SALES DATE]),
      FILTER( ALL( 'SALES '[SALES DATE] ), 'SALES '[SALES DATE] < MAX( 'SALES '[SALES DATE] ) ) )

This is the result that is showing

Any ideas about what I’m missing?

Thanks

Lucia

[lbarbeyto]

It seems like some customers don’t have purchases registered but they are in the sales table…

although some others purchased but still the field in the Last purchased date is blank

[sam.mckay]

Possible to attach the model here.

There’s just a lot at play here and it’s difficult to visualize everything without actually see the data.

Thanks

[lbarbeyto]

Hi Sam,

I sent you by email the link with the access to the model. As you will see there are blank dates for Last Purchase Date, therefore, the sporadic category is missing from the table.

I hope you can find my mistake

Thanks in advance for your help!

Lucia

[lbarbeyto]

Hi Sam,

I’ve trying to resolve this by myself, but I’m still struggling with the formula that I need.

I have created the measure named Purchased in by Current Date using this formula:

Purchased In by Current Date = // Number of months that each particular client purchased within the date selected.
VAR MonthlySales = SUMMARIZE( 'SALES DATA', 'CALENDAR'[Month & Year],
           "SalesAmt", [Total Net Sales] )

RETURN
CALCULATE( DISTINCTCOUNT( 'CALENDAR'[Month & Year] ),
    FILTER( MonthlySales,
          [SalesAmt] > 0 ) )

This measure is giving me the total number of months a customer is purchasing within the period I’m selecting. In this following example, I’m selecting Oct 2017 where customer 312 bought that same period.

After verifying in my excel file, everything works fine up to here…

The next step is to make the same calculation, but instead the current period I’m selecting, I need the calculation going back 12 months. For this, I created the following measure:

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

In this case, using the same customer as an example, the result now is showing 8 months.

After reviewing my excel file, I noted that there is one month that is not being included in the calculation, since the correct total should be 9 istead of the 8 months I’m getting.

Could you give me any idea about what is wrong with the formula I did? I would also need help about how to replace the blanks for zeros.

Thanks in advance for your help!

Lucia

[lbarbeyto]

Never mind Sam. I just realized that I was counting the periods wrongly.

[sam.mckay]

Awesome.

Formulas look great though. Nice work if you can get your mind around how those are working you are certainly on your way to really mastering DAX.

Chrs
Sam