topic Sum if customer has more than two invoices in last twelve m in DAX Commands and Tips

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

Anonymous — Thu, 27 Jan 2022 12:37:08 GMT

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?

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

amitchandak — Thu, 27 Jan 2022 14:09:43 GMT

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

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

Anonymous — Thu, 27 Jan 2022 14:49:45 GMT

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?

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

Anonymous — Fri, 28 Jan 2022 08:45:38 GMT

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

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