Microsoft Dynamics 365 is a robust tool with excellent reporting capabilities. Using Microsoft Dynamics 365 with Power BI as a supplemental reporting tool using your (well maintained) Dynamics 365 data opens doors to new formulas and data transformations. A Power BI client recently requested the ability to look at pipeline dollars (Opportunity Estimated Revenue) by the week it was forecast to close (Estimated Close Date). Estimated revenue by month wasn’t specific enough. They wanted to narrow it down a bit.
We can easily pull both data fields from Dynamics 365 but putting them together in a table as requested required making some changes to the query from Power BI. My column “Estimated Revenue” is presented as a date/time/time zone display. To create a new week column from our standard date column, first Duplicate the date column (we want to keep the original column for reference).
Rename the new column, then right click on the header and choose “Transform”. The Power BI Transform function frees us from the need to use formulas. All we have to do is select the desired date type. In this case we’re going choose “Week”.
You can see our new column displaying the week in which our Opportunities are closing.
Once again, Power BI to the rescue! Need help transforming or getting more out of your Dynamics 365 data? Contact or chat with Dyn365Pros.
By Erin Karatkiewicz, Dynamics 365 Application Consultant, Dyn365Pros, Microsoft Dynamics 365 Partner San Diego, Southern California.
