dax compare customers selected month vs other months

Question

I am trying to take a list of customers that purchased say in Jan 2019 and the from sales data count how many of those Jan customers purchased in Feb, March, etc.

Any ideas, I have tried many solutions presented but none of them seem to fit my needs

So that would need to return 2 Customers from Jan, 2 from Jan bought in Feb and only 1 from Jan bought in Mar.

Output to look something like this

Where Jan Purchasers are counted in Feb and March etc.

Can you show in a table how the OP should look. – ASN Feb 04 '20 at 10:31 — ASN, Feb 04 '20 at 10:31
Thanks ASN have edited with the output – smckechnie Feb 04 '20 at 10:39 — smckechnie, Feb 04 '20 at 10:39

score 1 · Accepted Answer · answered Feb 04 '20 at 15:49

Ok, I generated some random data in excel and pasted it in as a power query source:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZRLDsQwCEPv0nUXQNJ8zlLN/a8xGjWQ0UPdWhgbQ3Lfh0o/zsNEp4zjc/6AsgCVB1CvUFuAOaUtykDFbuoVE4A6RZwiDpRVUR3QBVysSNaV1r2prh5irz0uzCJvlB2QUsVACdnmPSozFVYMAFFhNHZRtkAlKkI2Qp4PYEKKwodG047Nxfgdq9QJYwG4rPIchLtNiW1jhefg41dSjLutVOHBbB/KgDpOOy07AqpsKvSRAipMXRHyPjqnNK5hcPz0gCYXJXC676NBVpLKIMBzCOtCCv8PS2+ff5CQsnsMpB63nh7h/y/1+QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Purchase YearMonth" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", Int64.Type}, {"Purchase YearMonth", Int64.Type}})
in
    #"Changed Type"

I think an effective approach is just to use DAX to add a column to your dataset that will 'tag' each row with that customers earliest purchase date. This is a calculated column, not a measure.

Earliest Purchase YearMonth = 

CALCULATE(
MIN('Sales'[Purchase YearMonth]),
FILTER(
   'Sales',
   Sales[Customer] = EARLIER(Sales[Customer]) 
))

Then just use a matrix visual with Earliest Purchase date in the row headers, purchase date in the column headers, and a count distinct of Customer as values.

Hope it Helps!

dax compare customers selected month vs other months

1 Answers1