Filter for a specific date (-30 days<specific date<90 days)

[Anonymous]

Hi community,

I have had a little difficulty in creating a filter that I can apply to the entire page to see only the information 30 days before and 90 days after the launch date of each product.

I have a database in which I can see the daily shipments of each product and another table that contains the release date of each product. What I want is to create a formula that calculates the date difference -30 days and +90 days and that only shows me the sum of the shipments of that period depending on when the product was launched.

Hope to get an idea 

Thank you in advance

[BA_Pete]

Hi @Anonymous ,

I would add new columns to the table where your release dates are stored in Power Query like this:

// openDate - 30 days before:
Date.AddDays([releaseDate], -30)

// closeDate - 90 days after:
Date.AddDays([releaseDate], 90)

Then in your DAX measures you can use these dates to dynamically select this window when selecting different products in the report, something like this:

_windowSales =
CALCULATE(
  SUM(factTable[Sales]),
  factTable[salesDate] >= productTable[openDate],
  factTable[salesDate] <= productTable[closeDate]
)

There's some considerations around relationships that I can't address without seeing your model (I've assumed you have a relationship between productTable and factTable based on [Product]), but the principle I've outlined is how I would go about it.

Pete