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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Report for Opportunities closing in 0-30 days, 31-60 days, 61-90 days & > 90 days

Hello Everyone, 


I just started using Power Bi but I have some experience with it, not as much as id like. I built out a dynamics CRM for sales and I decided to make weekly sales reports in Power Bi rather than SSRS-BIDS SQL reporting. 


I have created a sample page where I have 3 tables stacked on top of each other. It reads like: 

Opportunities Closing in the Next 30 Days

  • currently shows all opportunities from today's date with [est. close date] "is in the next" 30 days

Opportunities Closing in the Next 60 Days

  • currently shows all opportunities from today's date with [est. close date] "is in the next" 60 days
    • shows duplicates of today- day 30, then new after 31-60

Opportuntites Closing in the Next 90 Days

  • currently shows all opportunities from today's date with [est. close date] "is in the next" 90 days
    • shows duplicates of today- day 60, then new after 61-90

I am using the following fields in each table 

[Topic] [Account] [Est. Close Date] [Pipeline Phase] [Owner] [Date of Last Activity] [Est Revenue]


Screen Shot 2020-09-15 at 7.34.40 PM.png

I want each table to show Opportunities without the duplicates. Sort of like binning data. 

Im looking to display opportunities closing in (0-30)(31-60)(61-90) days not (0-30)(0-60)(0-90) with three different tables. 


Where do I start? Do I need to do this with code since the filters in power bi don't give me ability to select [est. close date] after X days and before Y days?


It seems like this should be relatively easy. 

Thank you so much for your help. 

All the best, 




Frequent Visitor

Update: I pulled all the data into Power BI Desktop with the entities I wanted from the XRM Toolkit using the Power Query BI app. I then added a calendar entity to my data set using the help of this guide. I then took the concept discussed in this youtube video to make a "Relative Day Filter". 

My Code for the custom column is... 

Relative day filter =

I made this off my "Day of Year" column in my calendar. This gets me close but not perfect. I think I need to include the "Est. Close Date" from the opportunity entity somehow and calculate a date diff from this. Or I need to map a relationship somehow between the calendar and the opportunity to make this work. 
Any ideas?

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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