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

[Anonymous]

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?

[amitchandak · Accepted solution]

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

[Anonymous]

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]

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

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