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?
