Forum Discussion

arpand365's avatar
arpand365
New Member
10 months ago
Solved

Date range on Power BI report

Hello, I am new to this community. I am D365F&O Developer. And I also new to Power BI report. I am using Power BI Desktop.   As per our requirement, I need put date range on Vendor transaction.   ...
General Comment
  • Anonymous's avatar
    Anonymous
    10 months ago

    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.

     

  • Praful_Potphode's avatar
    Praful_Potphode
    10 months ago

    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.

    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:

    1. Enable Public API: You must select the option Enable public API in the wizard.

    2. 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:

    1. Log in to D365 Finance & Operations.

    2. Go to Workspaces > Data management.

    3. Click the Framework parameters tile.

    4. Select the Entity settings tab.

    5. 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:

    1. Open Power BI Desktop.

    2. Go to Get Data > OData Feed.

    3. Enter your URL: https://[environment].operations.dynamics.com/data.

    4. 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-integration 

    https://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

Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
10 months ago

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

 

  • Aroand365's avatar
    Aroand365
    New Member
    10 months ago

    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's avatar
      Praful_Potphode
      Icon for Super User rankSuper User
      10 months ago

      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.

      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:

      1. Enable Public API: You must select the option Enable public API in the wizard.

      2. 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:

      1. Log in to D365 Finance & Operations.

      2. Go to Workspaces > Data management.

      3. Click the Framework parameters tile.

      4. Select the Entity settings tab.

      5. 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:

      1. Open Power BI Desktop.

      2. Go to Get Data > OData Feed.

      3. Enter your URL: https://[environment].operations.dynamics.com/data.

      4. 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-integration 

      https://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