Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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])
, PREVIOUSMONTH(DATEADD('Calendar'[Date], -1, YEAR)))

return IF(_lastmonthsales > _lastmonthlastyearsales, 1, 0)

1 ACCEPTED SOLUTION
Frequent Visitor

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.

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

Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

Frequent Visitor

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.

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors