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

Reply
dapronr
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!!

 

dapronr_0-1617132834661.png

 

 

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

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.

dapronr_0-1617303314552.png

 

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
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.

AllisonKennedy
Super User
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

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.

dapronr_0-1617303314552.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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