Customer experience | Sales, Customer Insights,...

Using Dynamics Sales Power BI connector App filters for reports 30, 60, 90, >90 days

(0) Share

Report

Posted on by Dribblej

265

Hello,

I am looking to make 3 separate tables in a Power BI report using data from Dynamics.

Here is a version of the tables.

Screen-Shot-2020_2D00_09_2D00_15-at-7.34.40-PM.png

Im wondering if I can use the out of the box filtering for these tables so opportunities are not double counted across them.

Example: Filter would use estimated closing date

Advanced Filtering:
Show Items when the value is on Today and is on or before 30 days
Filter: Show items when the value is On or after "Todays Date + 30" AND On or Before "Todays Date + 60"
Filter: Show items when the value is On or after "Todays Date +60" AND On or before "Todays Date +90"

Is this possible with the OOB editing on filters within the PowerBI website?

There is also an OOB entity called Date with some options.

Does anyone have and idea where you can filter a range of days out in the future like this.

Thank you for your time,

Josh

I have the same question (0)

All responses (2)

Answers (0)

Dribblej 265 on at

Like (0)

Report

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 some how 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?

Was this reply helpful? Yes No
Suggested answer

Dribblej 265 on at

Like (0)

Report
I was able to solve this. With the following steps.

Create a custom Power BI report on desktop version using XRM Toolkit - Power Bi Power Query Builder

Pulled in relevant entities [lead,contact,account,opportunities, owners, product, opportunityproduct] + Dyn365CEBaseURL & ServiceRootURL bc of how i pulled in the data.

Created a Calendar entity

add Day of the year column

Make a relationship between est. close date and date, between calendar and opportunities

Create a custom column on the Opportunties entity

Est. Close Filter = DATEDIFF(TODAY(),'Opportunities'[Est. Close Date],DAY)

You can now add a slicer to your list of opportunities.

add the slicer to your power bi page grab the new entity field you just created of est. close the filter and drop it on your slicer

apply it to lists in various ways

to create a list "opportunities closing in 30-60 days set your filter to 30-60

Alternatlivley you can just drag this new field into the visual filters, and then use advanced filtering to sort day ranges like 30-60 days

Advanced filter on est. close date filter -> "is greater than 30 AND is less than or equal to 60"

Was this reply helpful? Yes No

Join the conversation

Community site session details

Using Dynamics Sales Power BI connector App filters for reports 30, 60, 90, >90 days

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

Featured topics

Product updates

Community site session details

Using Dynamics Sales Power BI connector App filters for reports 30, 60, 90, >90 days

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Customer experience | Sales, Customer Insights, CRM

Featured topics

Product updates