Skip to main content

Notifications

Announcements

No record found.

Small and medium business | Business Central, N...
Suggested answer

Power BI reporting - automation

Posted on by 29

All,

I'm working on a report for my Finance department, and I wanted to know if there is a way I can get historical data from using the PBI connector? When I import the data by using "Get Data" it only imports data based on the current day of import. How do I get the historical data, say, from the beginning of the year or any time prior to that? Is it possible just using the PBI connector or will I have to use another method?

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,565 Super User 2024 Season 2 on at
    RE: Power BI reporting - automation

    Hi, generally, you will get all the data, instead it will be a bit troublesome to get only the data of the day.

    For example,

    pastedimage1676421362683v1.png

    Select the environment, company, and data you need to connect.

    pastedimage1676421404935v2.png

    For example, if you choose generalLedgerEntries, you will get the data of all posting dates by default.

    pastedimage1676421458929v3.png

    Hope this helps.

    Thanks.

    ZHU

  • swebb111 Profile Picture
    swebb111 29 on at
    RE: Power BI reporting - automation

    Both GL and Accounts tables are empty.

    Edit: This was untrue, I just hadn't refreshed the dataflow and then refreshed my pbi desktop report. Apologies for any confusion.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: Power BI reporting - automation

    The General ledger entries, and chat of accounts are the two tables i would use.

    The you can put a filter on date in PBI and have full flexibility.

  • swebb111 Profile Picture
    swebb111 29 on at
    RE: Power BI reporting - automation

    I'm not sure what you mean by what kind of data flow, just the standard one I guess in the Power BI service. I'm using BC Online. Customer lives off of trial balance data and has requested that, but I'll look at general ledger and see if that might offer a better picture of the data.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: Power BI reporting - automation

    What kind of dataflow are you using?

    Are you using BC online or on prem?

    You should really use the general ledger entries as the source for these kind of reports unless you have a more customized accumulation to use.

  • swebb111 Profile Picture
    swebb111 29 on at
    RE: Power BI reporting - automation

    My data source is Business Central, specifically the trialBalances page(s) for each environment, and I'm using a data flow to pull that data. However, when I pull the data, it only filters for today's date. I'm wanting to pull last years data and all data current to today, if that makes sense.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: Power BI reporting - automation

    Depending on what you use as your data source in your PBI report you should really get all data from BC.

    What have you selected as your source?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans