count number of customers with > sales last month vs sales last month last year

[dapronr]

Seems easy enough on paper 🙂 This calculation seems easy but i'm missing something in approach i suspect.

Need to count the # of customers with total sales last month > total last month last year sales and be dynamic on date (month) selected in slicer.

The source is our sales fact table that is joined to date table. 

This is how i approached the problem but then unable to count the number of customers that = 1

Should i create a sperate table with SUMMARIZE columns? I've tried but can't seem to work out the logic if that is the correct approach. And if yes, how should i aggregate the time dimension in relation to sales in column format? 

If DAX solution then what am i missing??

Thoughts and suggestions appreciated!!

A1-sales last month VS Last Year =

var _lastmonthsales =

CALCULATE(

SUM(INVCDTLS[EXTENDED_PRICE])

, PREVIOUSMONTH('Calendar'[Date]))

var _lastmonthlastyearsales =

CALCULATE(

SUM(INVCDTLS[EXTENDED_PRICE])

, PREVIOUSMONTH(DATEADD('Calendar'[Date], -1, YEAR)))

return IF(_lastmonthsales > _lastmonthlastyearsales, 1, 0)

[Anonymous]

Hi  @dapronr  ,

You can mark the correct answer as a mark for future reference.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@dapronr You need to provide row context of customer table and you can SUM the result to count the 1s:

RETURN

SUMX(CustomerTable, IF(_lastmonthsales > _lastmonthlastyearsales, 1, 0) )

[dapronr · Accepted solution]

thanks for the reply -

although not the full solution your SUMX pushed me in the right direction with row context via iterations.

variables not necessary

Used SUMMARIZE in combination with SUMX. here is the final solution.