Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Proud to be a Datanaut!