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
danno
Resolver V
Resolver V

Time shifting of reports against Dynamics 365 Direct Query

Hi,

I am working with the Dynamics 365 for operations PowerBI content, and attempting to do a report where the user can select the period and the report adapts to the user selection.  This is natural powerbi territory, except the measures and the way they work in the out of the box reports with direct query seems limited, due to the way the reports use the fiscal calendar table.

The Fiscal Calendar table from Dynamics has a set of columns for offsets, for month (period), Quarter and Year, where each row has an offset relative to the current date.  The measures for e.g 'Amount last period' use a filter of 'Period Offset = -1'.  So if you want to do reporting where a user selects a period, then the measures become useless as they are relative to the current date so end up blank when filtered by the selected period.  I am tempted to import the Fiscal Calendar table again as a disconnected table (to provide a separate date table that doesn't filter all rows) and change the measures to use Parallel Period to shift the measures relative to the selected date from the new table. 

Has anyone else tried this approch - and does it work in a performant way when using Parallel Period with Direct Query?  Or is there another option?

1 REPLY 1
v-yuezhe-msft
Employee
Employee

@danno,

Could you please post sample data of your  table and post expected result here? Do mask sensitive data before uploading sample data. 

In addition, please note that there is no DirectQuery for Dynamics.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors