Flag to show no sales in last 4 months for a "Customer" by "State" and "Product"

[bjosic]

Hi Community

I'm newbie to the world of DAX and I'm hopping someone can assist me. 

I require a DAX calculation (if it's possible) to give me a flag when a customer has no sales of a product within the last 4 months for their state's product ranging only.

I've been able to get the below to work to find the gaps but they do not allow state specific products, It shows everything.

All my sales are in one query table "Actuals"

Actuals[9LE] = sales

Actuals[CUSTOMER_DIM.CUSTOMERNAME] = Customers

Actuals[STATE] = State

Actuals[ALMCode] = Products

For for months I used a rolling sales calculation, and variations for 6 and 12;

=VAR EndDate=MAX(Dates[DATE])
VAR StartDate=EDATE(EndDate,-3)
VAR Result=
CALCULATE(
SUM(Actuals[9LE]),
DATESBETWEEN(Dates[DATE],StartDate,EndDate)
)
RETURN
Result

Then I used the below to flag the issues;

=IF([Last 12Mths 9LE]<=0,0,IF(OR([Last 6Mths 9LE]<=0,[Last 6Mths 9LE]>0&&[Last 4 Mths 9LE]<=0),1,0))

I could be going totally down the wrong path. Happy to rewrite it all if required.

Appreciate your help.

Thanks Brad

[amitchandak]

@bjosic , last 4 months sales for any combination. With a separate date tbale

Rolling 4= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-4,MONTH))

No sales for last 4 month = if(isblank([Rolling 4]), 1, 0)

a approch I followed here

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

[bjosic · Accepted solution]

Thanks @amitchandak 

I do have a seperate date table already.

But solultion will not work, I only want to flag a customer if the customer has not purchase in the last 4 months. The formula above shows a flag if the customer has also have never purchased it, not what I'm looking for.

I did get around that issue with adding in rolling 12 and changing the lost sales to show

IF(ISBLANK([Rolling 12 months]),0,IF(ISBLANK([Rolling 4 Mths]),1,0))

But this still leaves me in the exact same spot as I was in the beginning. The cutomer report still shows all customers and products, not just the ones ranged for that state.

I need to somehow restrict the products for that state and for the customers with that state.

Regard

Brad