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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Dribblej
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, 

Josh 

 

 

1 REPLY 1
Dribblej
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 =

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?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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