Spend Groups based on dynamic month selection

DAX DAX Calculations
1

Background : I am calculating rolling 12 months spend for our supplier database based on dynamic month selection.

formula used Measure12 = CALCULATE([Spend],DATESINPERIOD(Dates[Date],MAX(Dates[Date]),-12,MONTH))

I created groups to be able to break spend in buckets :
image

and dax formula

Spend Buckets = 
CALCULATE([Measure12],
    FILTER(VALUES(Spendata[Supplier]),
        COUNTROWS(
            FILTER('Spend Groups',
            [Measure12]>='Spend Groups'[Min]
              && [Measure12]<'Spend Groups'[Max]))
>0))

ISSUE :

Spend Buckets Measure is ignoring the Suppliers that do not have spend in the Selected month.

2

Yes it will, because there won’t be any results for them.

If there’s no result for them how do you want them to be recognized with this calc, when there’s nothing to show?

When you Measure12 evaluates to zero for someone there’s no too much that can be shown.

Can you advise what you planned to show for the suppliers with zero?

3

My bad!
Suppliers do not have spend in the Selected Month ( ie September) but have spend in one or more of the previous 12 months.
I need the formula to group them by the last 12 months spend even if they do not have spend in the selected month.

4

Check out the formula below

Months Purchased In 12 months back = // Number of months that each particular client purchased in within the last 12 months from the date selected.
VAR MonthlySales = 
CALCULATETABLE(
    SUMMARIZE( 'CALENDAR', 'CALENDAR'[Month & Year], "SalesAmt", [Total Net Sales] ),
        DATESINPERIOD( 'CALENDAR'[Date], MAX( 'CALENDAR'[Date] ), -1, YEAR ) )
RETURN 
COUNTROWS(
    FILTER( MonthlySales, [SalesAmt] > 0 ) )

This came this forum thread.

Chrs