cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Calculate if any month greater than value

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.

1 ACCEPTED SOLUTION
Super User

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 (
SUMMARIZE ( Sales, Customer[Customer], 'Calendar'[Month-Year] ),
"@recentfourmonthsmonthlysales", [Recent four months sales:]
),
[@recentfourmonthsmonthlysales] > 100000
),
Customer[Customer]
)
) + 0``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Super User

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 (
SUMMARIZE ( Sales, Customer[Customer], 'Calendar'[Month-Year] ),
"@recentfourmonthsmonthlysales", [Recent four months sales:]
),
[@recentfourmonthsmonthlysales] > 100000
),
Customer[Customer]
)
) + 0``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.