Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 

 

1 REPLY 1
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors