Sum of Sales for last 3 months with sales (ignoring time filters)

[#1]

A bit new at this so bear with me.  I am trying to get a sum of sales for the last 3 months with sales

I have a month flag ([_dateflag_Monthly_CM]) assigned to the most recent month with sales so it seems like it should be easy but I am having a hard time nesting my filters in set analysis

Steps I need to take

Identify Month associated with [_dateflag_Monthly_CM]

Calculate sales for the last 3 months based on the flag.

Ignore any Month filters

Relevant fields

[_dateflag_Monthly_CM] - flag for most recent month with sales

[Monthly Sales] - sales metric

[Month-Year] - date field with only the 1st of every month

I've started to build the calc but am stuck.  What I have so far (may not be helpful)

sum( {<[Month-Year] >= {AddMonths([Month-Year],-3)} >} [Monthly Sales])

//{< [_dateflag_Monthly_CM]={1} >}

Any help would be greatly appreciated!

[jerem1234]

Try something like:

sum({<[Month-Year] = {'>=$(=AddMonths(only({1<[_dateflag_Monthly_CM] ={1}>}[Month-Year]), -3))'}>}[Monthly Sales])

Hope this helps!

[#3]

Thanks Jeramiah,

The formula evaluates fine but the result ends up being zero.  It shows as zero for all rows even when in a table with Month-Year.  I'm trying to figure out what logic is being applied and will update if I can determine.

[jerem1234 · Accepted solution]

Here's a small example of the formula I posted.

Hope this helps!

[#5]

Thanks for the Example!  this worked perfectly.  I had an issue with how my [Month-Year] was coming in that caused the issue. 

Thanks Again!