Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 , 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))
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?
Fixed it myself by adding a new measure: Total Sales Sum =
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |