cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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

return IF(_lastmonthsales > _lastmonthlastyearsales, 1, 0)

1 ACCEPTED SOLUTION
Frequent Visitor

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.

3 REPLIES 3
Community Support

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.

Super User

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

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Frequent Visitor

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors