Formula to select most recent date for rolling 12 months not working

[MikeGeno]

I am trying to create a measure for the last 12 months.  This formula works to a degree, but I am running into an issue. 

For example:  This finds the most recent date and counts back 12 periods to sum the sales.  The issue I am having is lets say I am trying to see this data for a 12 over 12 comparison based on Product Groups.  I have some discontinued products that may not have had any sales in the last 12 months so the result would be $0.  When looking at the visual, the formula is looking at my data and finding the max date for the sales for that product group.  So if 18 months ago was the last time a sale was recorded, it is using that date as the max for that group and counting back 12 months from there instead of the most recent month in my data source.

TYIA.

Last 12 M =

VAR EndDate = max(SalesData[SalesDate])

VAR StartDate = EDATE(EndDate,-12)

var Result =

calculate(SUM(SalesData[Sales]),

DATESBETWEEN(SalesData[SalesDate], StartDate +1,  EndDate)

)

RETURN

result

[Anonymous · Accepted solution]

Hi @ MikeGeno

I have understood about your problem, here is the solution I offer:

Start by creating a table about dates

Here's my sales sheet about the product

Here's a revised measure that should work for your scenario：

Here is the result

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.