Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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

 

 

 

Need Help

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    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