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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Sum if customer has more than two invoices in last twelve m

Hi there

 

I have managed to create a calculation of sales in the previous 12 months per customer using the following measure:

 

Total Sales Previous 12 Months =

VAR CurrentDate = SELECTEDVALUE('Date'[End of Month Date],MAX('Date'[End of Month Date]))

VAR PreviousDate = CurrentDate - 365

RETURN

CALCULATE(

[Total Sales],

FILTER(

'Budget and last year per month',

'Budget and last year per month'[Date] >= PreviousDate && 'Budget and last year per month'[Date] <= CurrentDate

)

)

 

However, I would like to add an additional filter that only shows customers with => 2 invoices in the selected period (last twelve months from selected date).

 

Any ideas on how I can add this to the above?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , assume measure working correctly , create count or invoice for 12 month

 

sumx(filter(values(customer[customer]) , [count or invoice] >2) , [Total Sales Previous 12 Months]  )

 

 

refer these examples for rolling 12

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , assume measure working correctly , create count or invoice for 12 month

 

sumx(filter(values(customer[customer]) , [count or invoice] >2) , [Total Sales Previous 12 Months]  )

 

 

refer these examples for rolling 12

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

That works beautifully! Thank you! The only problem now is that the row total sums to the same total as without the filter? Any idea why?

Anonymous
Not applicable

Fixed it myself by adding a new measure: Total Sales Sum =

SUMX(VALUES(Customer[Customer]), [Total Sales])

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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