Forum Discussion
Date range on Power BI report
Hi arpand365 ,
1. Put a Date Range on Vendor Transaction (TransDate)
Step 1. Import your BYOD tables
You will have at least two tables:
VendTable (Vendor header)
VendTrans (Vendor transactions)
In Power BI Desktop:
Go to Get Data → SQL Server
Connect to your BYOD database
Select:
VendTable
VendTrans
Load them into Power BI
Step 2. Create relationships
You need:
VendTable.AccountNum → VendTrans.AccountNum
A Date table connected to VendTrans.TransDate
Create a Date Table
Go to Modeling → New Table and enter:
Date =
ADDCOLUMNS (
CALENDAR (DATE(2015,1,1), TODAY()),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM"),
"MonthNo", MONTH([Date]),
"YearMonth", FORMAT([Date], "YYYY-
MM")
)
Create relationship: Date[Date] → VendTrans[TransDate] (Many-to-One)
Step 3. Put a Date Range Slicer
On the report:
Insert a Slicer visual
Drag Date[Date] into the slicer
Change slicer type → Between
2. Showing Calculation Value (a = b + c)
If your report needs a simple calculated measure (for example, a = b + c):
Option 1. Create a Calculated Column (row-by-row)
If b and c are columns in VendTrans:
a = VendTrans[b] + VendTrans[c]
Use this if the calculation is per transaction record.
Option 2 . Create a Measure (aggregated calculation)
If you need:
a = Total B + Total C
dynamically filtered by date, vendor, slicers, etc.
Then use a Measure:
Total B = SUM(VendTrans[b])
Total C = SUM(VendTrans[c])
Total A = [Total B] + [Total C]
Add Total A to any visual.
If my response as resolved your issue please mark it as solution and give kudos.
Hi Aroand365 ,
enabling Data Entities is the critical "bridge" step working with D365 Finance & Operations (D365F&O). If you do not perform this refresh, your OData feed in Power BI will be empty or missing new custom entities.
Here is the walk-through based on the official Microsoft Learn documentation.
The specific logic you are looking for is found in the "Data entities overview" and "Build and consume data entities" documentation.
Primary Reference: Data entities overview - Finance & Operations | Dynamics 365.
Developer Reference: Build and consume data entities.
Step-by-Step Walkthrough: Enabling Entities for Power BI
There are two sides to this: the Developer side (making the entity capable of being seen) and the Admin side (refreshing the system to actually show it).
1. Developer Side: The "IsPublic" Property
If you are looking for a standard entity (like CustomersV3), it is likely already enabled. However, if you created a Custom Entity, you must set specific properties in Visual Studio during development:
Enable Public API: You must select the option Enable public API in the wizard.
Public Collection Name: You must define a Public collection name and Public entity name.
Why? The OData feed uses these public names, not the backend AOT name. If IsPublic is set to "No", Power BI cannot see it.
2. Admin Side: The "Refresh Entity List" (Crucial Step)
Even if an entity is "Public," Power BI will not see it immediately. You must force D365F&O to update its list of available OData endpoints.
Navigate to the Data Management Workspace:
Log in to D365 Finance & Operations.
Go to Workspaces > Data management.
Click the Framework parameters tile.
Select the Entity settings tab.
Click Refresh entity list.
Note: This process runs in the background and usually takes 1–10 minutes depending on the environment. You will not get a pop-up when it finishes; you simply have to wait before the entity appears in Power BI's Navigator.
3. Verifying in Power BI
Once the refresh is complete:
Open Power BI Desktop.
Go to Get Data > OData Feed.
Enter your URL: https://[environment].operations.dynamics.com/data.
Your entity should now appear in the list using the Public Collection Name you defined.
refer the official documentation :
https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/power-bi-integrationhttps://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/odata
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
9 Replies
- Ritaf1983
Super User
Hi arpand365
You can handle both requirements easily once your Power BI model is set up correctly.
Here’s the recommended approach:1. Adding a date range filter on Vendor Transaction Date
In Power BI, the standard way to work with dates is to create a proper Calendar (Date) table and connect it to your transaction table.
Steps:
Create a Calendar table that contains every date in your data range.
Mark it as a Date Table in Power BI.
Create a relationship between the Calendar table and your Vendor Transactions table using the transaction date field.
Now you can place a Date slicer on the report.
The built-in slicer supports:a date-picker
a slider
between / before / after modes
This slicer will filter your Vendor Transactions automatically via the relationship.
2. Showing calculated values in the report (using DAX measures)
All calculations in Power BI should be done using measures.
If your calculation is simple (one measure plus another):
A =
[B] + [C]
If your calculation needs to be done row-by-row (for example: Quantity × Price per transaction), use SUMX:Total Amount =
SUMX (
'VendorTransactions',
'VendorTransactions'[Quantity] * 'VendorTransactions'[Price]
)
SUMX iterates through each row of the table and then aggregates the result — very useful for transaction-level calculations.You can then place the measure in any visual (table, matrix, card, etc.).
Useful beginner resources :
https://www.youtube.com/watch?v=Z2t7l8b1uWU
https://www.youtube.com/watch?v=MhC4zj2byBQ
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thanks Ritaf for the response.
Any logic either complex or simple, where I have to write in Power BI?
Could you please give me an reference, where data is puching from D365F&O into Power BI and business logic is written in Power BI itself to display in report.
I am new to Power BI,that's why looking for reference from scratch to made the connection between power bi and d365fo.
Thanks for understanding and support.
- Praful_Potphode
Hi Aroand365 ,
enabling Data Entities is the critical "bridge" step working with D365 Finance & Operations (D365F&O). If you do not perform this refresh, your OData feed in Power BI will be empty or missing new custom entities.
Here is the walk-through based on the official Microsoft Learn documentation.
The specific logic you are looking for is found in the "Data entities overview" and "Build and consume data entities" documentation.
Primary Reference: Data entities overview - Finance & Operations | Dynamics 365.
Developer Reference: Build and consume data entities.
Step-by-Step Walkthrough: Enabling Entities for Power BI
There are two sides to this: the Developer side (making the entity capable of being seen) and the Admin side (refreshing the system to actually show it).
1. Developer Side: The "IsPublic" Property
If you are looking for a standard entity (like CustomersV3), it is likely already enabled. However, if you created a Custom Entity, you must set specific properties in Visual Studio during development:
Enable Public API: You must select the option Enable public API in the wizard.
Public Collection Name: You must define a Public collection name and Public entity name.
Why? The OData feed uses these public names, not the backend AOT name. If IsPublic is set to "No", Power BI cannot see it.
2. Admin Side: The "Refresh Entity List" (Crucial Step)
Even if an entity is "Public," Power BI will not see it immediately. You must force D365F&O to update its list of available OData endpoints.
Navigate to the Data Management Workspace:
Log in to D365 Finance & Operations.
Go to Workspaces > Data management.
Click the Framework parameters tile.
Select the Entity settings tab.
Click Refresh entity list.
Note: This process runs in the background and usually takes 1–10 minutes depending on the environment. You will not get a pop-up when it finishes; you simply have to wait before the entity appears in Power BI's Navigator.
3. Verifying in Power BI
Once the refresh is complete:
Open Power BI Desktop.
Go to Get Data > OData Feed.
Enter your URL: https://[environment].operations.dynamics.com/data.
Your entity should now appear in the list using the Public Collection Name you defined.
refer the official documentation :
https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/power-bi-integrationhttps://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/odata
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
Hi arpand365 ,
1. Put a Date Range on Vendor Transaction (TransDate)
Step 1. Import your BYOD tables
You will have at least two tables:
VendTable (Vendor header)
VendTrans (Vendor transactions)
In Power BI Desktop:
Go to Get Data → SQL Server
Connect to your BYOD database
Select:
VendTable
VendTrans
Load them into Power BI
Step 2. Create relationships
You need:
VendTable.AccountNum → VendTrans.AccountNum
A Date table connected to VendTrans.TransDate
Create a Date Table
Go to Modeling → New Table and enter:
Date =
ADDCOLUMNS (
CALENDAR (DATE(2015,1,1), TODAY()),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM"),
"MonthNo", MONTH([Date]),
"YearMonth", FORMAT([Date], "YYYY-
MM")
)
Create relationship: Date[Date] → VendTrans[TransDate] (Many-to-One)
Step 3. Put a Date Range Slicer
On the report:
Insert a Slicer visual
Drag Date[Date] into the slicer
Change slicer type → Between
2. Showing Calculation Value (a = b + c)
If your report needs a simple calculated measure (for example, a = b + c):
Option 1. Create a Calculated Column (row-by-row)
If b and c are columns in VendTrans:
a = VendTrans[b] + VendTrans[c]
Use this if the calculation is per transaction record.
Option 2 . Create a Measure (aggregated calculation)
If you need:
a = Total B + Total C
dynamically filtered by date, vendor, slicers, etc.
Then use a Measure:
Total B = SUM(VendTrans[b])
Total C = SUM(VendTrans[c])
Total A = [Total B] + [Total C]
Add Total A to any visual.
If my response as resolved your issue please mark it as solution and give kudos.
- PhilipTreacy
Hi arpand365
It's not clear what you are trying to do. Please supply some data in text format (not screenshot) or preferably your PBIX file, and show examples of the desired result.
Regards
Phil
- Rufyda
Hi,
Connecting D365 F&O to Power BIEnable Data Entities in D365 F&O (set IsPublic = Yes).
Refresh the entity list in D365 Data Management workspace.
In Power BI Desktop:
Get Data → OData Feed
Enter your environment URL: https://[env].operations.dynamics.com/data
Load the entities.
References
Power BI integration with D365 F&O
Beginner DAX tutorial
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Regards,
Rufyda Rahma | Microsoft MIE 
v-ssriganeshCommunity Support
Hi arpand365,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Rufyda, Kedar_Pande, Praful_Potphode & Anonymous for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
- v-ssriganesh
Hello arpand365,
Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.
Thank you.
