Calculate if any month greater than value

[plover]

Hi there

I am attempting to write a report that will allow me to see any accounts that have a spending amount per month OVER a certain amount. If they do, then they are eligible for discounts etc.

I am looking for a way to look back say 12 months, and if a customer has ANY month with an amount greater tha 100,000 then return them - even if it was only 1 month.

My current measure only works if the TOTAL for the 12 months is over the amount, so not quite what i was after. Any help appreciated.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file and the sample measures like below. I amended the question to check recent four months sales per each customer, but I hope you can change this to recent 12 months in your datamodel.

I hope the below sample can provide some ideas on how to create a solution for your datamodel.

Sales measure: = 
SUM(Sales[Sales])

Recent four months sales: = 
CALCULATE (
    [Sales measure:],
    KEEPFILTERS (
        DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), 0 ), -4, MONTH )
    )
)

Expected reult measure: = 
COUNTROWS (
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE ( Sales, Customer[Customer], 'Calendar'[Month-Year] ),
                "@recentfourmonthsmonthlysales", [Recent four months sales:]
            ),
            [@recentfourmonthsmonthlysales] > 100000
        ),
        Customer[Customer]
    )
) + 0

[amitchandak]

@plover .Assume you have measure M1 and customer dimension and Date Table

Rolling 12 = calculate(Countx(Filter( Values(Customer[Customer]), [M1] >100000 ) ,[Customer]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 12 should 1 or more or not blank