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

[Dribblej]

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]

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, 

Josh 

[Dribblej]

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 =

DATEDIFF(TODAY(),'Calendar'[Date],DAY)

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?