Data selection for latest 'available' month

[minhvuong93]

Hello everyone,

I have a dataset of 12 months in 2017.

Customer ID _ Sales volume _ Month

A1                  _    100            _  Jan

A2                  -   50               _ Jan

A3                   -  120              _ Jan

-.....

A1               _ 120 __             Dec

A2                _30 _                 Dec

I would like to filter list of customer remaining in December. (as some customers quit during the year)

Then calculate the average volume sales of the whole year (not only Dec)

How can I accomplish this?

Thanks so much if you can help me, 🙂

[Greg_Deckler · Accepted solution]

Easiest thing to do would be to create a measure like:

AverageLatest = CALCULATE(AVERAGE(CustomersLatest[Sales]),ALLEXCEPT(CustomersLatest,CustomersLatest[CustomerID]))

Then create a table visualization with CustomerID. Drage Month to your visual filter area and filter to "Dec". Then, drag in your AverageLatest measure.

[minhvuong93]

@Greg_Deckler

Also , could you help me to explain,

what if the customers have the 1st sales in , lets say, OCT and DEC , but no sales in NOV.

So for AVERAGE in this case, will it be: total sales divided by 2 or by 3 month?

[Greg_Deckler]

The AVERAGE function is going to take care of all of that and when you put it in a table for each customer, it will be per customer. So, if one customer has 5 entries and another 2 and another 12, AVERAGE will calculate the average correctly, no worries.

[minhvuong93]

@Greg_Deckler

Thanks for helping quickly,

Can I add a filter into this measure to ensure the volume >0

since some of the order quantity might be < 0 (back orders, return goods...)

Average_volume= CALCULATE(AVERAGE(customers[OrderQuantity]),customers[OrderQuantity]>0,ALLEXCEPT(MK,MK[customersID]))

[Greg_Deckler]

Yes, that should work.

[minhvuong93]

@Greg_Deckler thanks for your quick reply,

What is the "CustomerLatest" from?

Does that mean i have to create a new table with only Dec?

[Greg_Deckler]

No, that's just the name I gave my table when I recreated your data.