Sum the Count to Get Month Over Month

[ilearngood]

I have a column "purchase date" that has dates and null/blank values by customer (have another column named "customer").

Dates range is 1/1/2014 to 2/13/2017.

Goal is to create a column that sums the "purchase date" count of purchases for those that are not null by month to get to month over month differences.  

So 'Customer A' count of purchases in Jan 2017 is 25, and 20 in Dec 2016.  I need the new column to calculate the difference of 5.

Your assistance is appreciated.

[v-yulgu-msft · Accepted solution]

Hi @ilearngood,

Suppose your dataset is like:

Based on this sample data, I created a new table which is filtered without null/blank values.

Filter non blank =
CALCULATETABLE (
    'Count purchase',
    'Count purchase'[Purchase Dates] <> BLANK ()
)

In this new table, I added two extra columns:

Yer_Month =
'Filter non blank'[Purchase Dates].[Year] & "-"
    & 'Filter non blank'[Purchase Dates].[MonthNo]


Previous Month =
IF (
    'Filter non blank'[Purchase Dates].[MonthNo] <> 1,
    'Filter non blank'[Purchase Dates].[Year] & "-"
        & 'Filter non blank'[Purchase Dates].[MonthNo] - 1,
    'Filter non blank'[Purchase Dates].[Year] - 1
        & "-"
        & 12
)

Then, I created another table:

Table1 =
SUMMARIZE (
    'Filter non blank',
    'Filter non blank'[Customer],
    'Filter non blank'[Yer_Month],
    "Previous Month", LASTNONBLANK ( 'Filter non blank'[Previous Month], 1 ),
    "Count Value", COUNT ( 'Filter non blank'[Purchase Dates] )
)

In Table1, I created two calculated columns:

Previous Month Count =
LOOKUPVALUE (
    Table1[Count Value],
    Table1[Yer_Month], Table1[Previous Month],
    Table1[Customer], Table1[Customer]
)

Difference Month Over Month =
Table1[Count Value] - Table1[Previous Month Count]

Below was the result what I got:

If I have something misunderstood, please share your sample data and provide more detailed information about your expected output.

Best regards,
Yuliana Gu