D365 Snippets & Power BI

Introduction:

Row-Level Security (RLS) in Power BI allows you to restrict data access for specific users based on defined roles. In this blog, we will demonstrate how to create and implement an RLS role named "country_filter" to restrict sales data visibility based on user countries. This is achieved using the DAX function: [Username] = USERPRINCIPALNAME()

Step 1: Setting Up Your Data Model

Before implementing RLS, ensure your Power BI model has the following:
Sales Table - Contains sales data with a Country column.
User Table - Contains Username and Country.
Relationship - Establish a relationship between the Country column in both tables (many-to-one relationship).

Step 2: Create a Security Role

1. Navigate to the "Model" View:

In Power BI Desktop, go to the "Model" view to work on roles and relationships.

2. Access the Security Settings: 

On the "Modeling" tab, select Manage Roles.

3. Create a New Role:

Click Create and name the role as country_filter. Select the User Table from the list of tables.

4. Define the DAX Expression:

In the "Table Filter DAX Expression" box, write the following expression:

[Username] = USERPRINCIPALNAME()

5. Save the Role:

Click Save to finalize the role.

Step 3: Test the Role in Power BI Desktop

1. Simulate User Access:

On the "Modeling" tab, select View As Roles.

2. Choose the country_filter role.

Enter a username (email) to test if the filtering works correctly.

3. Validate Results:

Check if only the sales data for the corresponding country is displayed.

In our project, we have created five email ids as username and 5 different nationalities, i am using [email protected] to filter the data by the country United States of America as shown below,

When we Test the Role in Power BI Desktop, only the sales data for the corresponding country aginst the username is displayed as below,

Step 4: Publish to Power BI Service

1. Publish the Report:

Save and publish your Power BI file to the Power BI Service.

2. Assign Users to the Role:

Go to your dataset in Power BI Service.

Click on the ellipsis (...) > Security.

Select the country_filter role and add email addresses of users who should have this access.

Step 5: Execute and Test RLS in Power BI Service

1. Verify Access:

Log in as one of the assigned users.

Open the report in Power BI Service and confirm that only the relevant country’s data is visible.

2. Iterate and Fine-Tune:

Make necessary adjustments to the data model or DAX expression based on feedback.

Conclusion

Implementing RLS with dynamic user filtering enhances the security and usability of Power BI reports. By following the steps outlined in this guide, you can easily control data access and provide a tailored experience for users.

Let us know how RLS improved your report's security or reach out with any questions in the comments! 🌟

A calendar table is essential for working with time-based data in Power BI. Whether you're analysing trends, creating time intelligence measures, or segmenting data into meaningful periods, a calendar table provides the foundation for these insights. In this blog post, we’ll explore how to create a robust calendar table using DAX in less than 10 seconds and understand the significance of its components.

Why Do You Need a Calendar Table?

Time is a critical dimension in most analyses. A calendar table allows you to:

• Filter and group data by year, quarter, month, or day.
• Enable time intelligence functions like year-over-year (YOY) growth or cumulative totals.
• Handle custom attributes like workdays, weekends, or fiscal calendars.

The DAX Code for a Calendar Table

Below is the DAX code to create a dynamic calendar table:

...

Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Month",FORMAT([Date], "mmm"),
    "Monthnum", MONTH([Date]),
    "Weekday", FORMAT([Date], "ddd"),
    "Qtr", "Q-" & FORMAT([Date], "Q"),
    "WeekType", IF(WEEKDAY([Date]) = 1 || WEEKDAY([Date]) = 7, "Weekend", "Weekday")
)

...

Understanding the Code

1. Base Calendar Table

The function CALENDARAUTO() generates a table of dates automatically, covering the entire range of dates present in your dataset. This ensures your calendar is dynamic and adapts to changes in your data model.

2. Adding Custom Columns

Using ADDCOLUMNS(), we enhance the base calendar table with additional attributes:
Year : Extracts the year from the date using the YEAR function.
Month: Formats the date to show the abbreviated month name (e.g., Jan, Feb).
Monthnum: Extracts the numeric month value (1 for January, 2 for February, etc.).
Weekday: Displays the day of the week as a short name (e.g., Sun, Mon, Tue).
Qtr: Constructs the quarter string using FORMAT (e.g., Q-1, Q-2).
WeekType: Categorizes the day as either Weekend or Weekday based on its value.

How to Use the Calendar Table

1. Create the Table: Open the Modelling tab in Power BI, select New Table, and paste the DAX code.
2. Relate to Other Tables: Connect the Date column from the calendar table to the date field in your data tables.
3. Leverage in Visualizations: Use the custom columns like Year, Month, or Week Type to segment your data in visuals.

Benefits of a Custom Calendar Table

• Advanced Time Intelligence: Perform YOY, MOM (month-over-month), or cumulative calculations with ease.
• Custom Time Attributes: Define fiscal quarters, holidays, or special business days.
• Dynamic Updates: Automatically adapts to your dataset without manual adjustments.

Final Thoughts

Overview:

The Sales Performance Dashboard is designed to provide a comprehensive view of the sales operations, tracking performance against targets and monitoring collections. This dashboard is a crucial tool for sales managers, finance teams, and executive leadership to make informed decisions, identify trends, and address issues proactively. 

Key Metrics: 

Sales Target: The predefined goals set for a specific period.

Sales Invoice: The actual sales recorded through invoices within the same period. 

Collections: The actual cash received from customers against the invoices.

Dashboard Sections:

Summary View: 

Total Sales Target: Displays the cumulative sales target for the selected period. 

Total Sales Invoiced: Shows the total value of sales invoices generated. 

Total Collections: Reflects the total cash collected from customers. 

Key Performance Indicators (KPIs): 

Target Achievement Rate (%): (Total Sales Invoiced / Total Sales Target) * 100 

Collection Efficiency (%): (Total Collections / Total Sales Invoiced) * 100 

Outstanding Receivables: Total Sales Invoiced – Total Collections

Performance Analysis:

Target vs Invoice Comparison: A clustered column chart comparing the sales target against the invoiced sales. This section highlights whether the sales team is meeting, exceeding, or falling short of the target. 

Here we have two types of Target vs Invoice Comparison Reports. 

1. Target vs Invoice Comparison by Salesman: This section of the dashboard focuses on comparing the sales targets with the actual sales invoices for each salesman. It provides a detailed view of individual performance, helping managers identify top performers and those needing support or training. A clustered column chart  comparing the sales target against the invoiced sales for each salesmen. 

2. Target vs Invoice Comparison by Month:This section of the dashboard provides a month-by-month comparison of sales targets versus actual sales invoices. It helps in tracking the monthly performance trends, identifying seasonal patterns, and understanding the overall progress towards annual sales goals for each month.

Invoice vs Collection Comparison: Another  visualization that compares the sales invoiced to the collections. This helps in understanding the efficiency of the collection process and the cash flowstatus. 

Here we have only month wise Invoice vs Collection Comparison Reports. 

Invoice vs Collection Comparison by Month:This section of the dashboard provides a month-by-month comparison of sales invoices versus collections using a clustered column chart. It is designed to help track the efficiency of the collections process, ensuring that invoiced sales are being converted into actual cash received in a timely manner.

Trend Analysis:

Trend analysis section helps in identifying seasonal patterns and sales cycles. we have three types of trend analysis visuals Monthly/Quarterly Trends and Year-over-Year Comparison.

1. Monthly/Quarterly Trends: Line charts showing the monthly or quarterly trends for sales targets, invoices, and collections. This section helps in identifying seasonal patterns and sales cycles. 

2. Year-over-Year Comparison: Visuals comparing performance metrics year-over-year to assess growth and changes over time. in our visuals we made year-over-year comparison for invoices , which compares current year invoice value with previous year sales value month wise.

Interactive Filters: 

Year Filter: Allows users to select specific year (e.g., month, quarter, year) for analysis. 

Month Filter: Allows users to select specific month for analysis. 

Quarter Filter: Allows users to select specific quarter for analysis.Salesman Filter: Filters to view data for specific salesman.

Conclusion: 

This dashboard serves as a powerful tool to track sales performance, ensuring that targets are met, and collections are optimized. By leveraging the insights from this dashboard, stakeholders can make data-driven decisions to improve sales strategies and operational efficiencies.

In the previous article, i already shown what is exactly Power BI and what are the advantages of using power bi and why you should choose power BI. In this articles, let us see how to install power bi desktop software on your laptop machine. Now, there are many methods to download the power bi desktop software.

Method 1

One of easiest method is which I’ll be showing you here is you  just enter the URL Download Power BI Desktop. You can use this URL to download the power bi software. When you open the above link you can find download free option.

Now once you click on that it will ask you to open Microsoft Store.

when you click on open Microsoft Store and here you can see the Power BI Desktop Application will be open here. We kow that Power bi desktop is a completely free software. You don’t have to pay anything for that.

In my system i already installed Power BI desktop, thats why install button is not showing here. if When you click on install button and it will automatically install on your laptop PC here. Now the best part is if you try by this method every month, whatever updates are there. It will automatically update here. because microsoft launch new updates every month for power bi. So this is one easy method of downloading and installing. So, here comes the power here. 

Here, if you see, the launch option, since you  already have power bi installed on your machine. However, if you are doing it for the first time, you will get here as installed, you will just click on install. It will take few minutes to install download and install. You can click on this launch button here. That’s one method.

Method 2

Second method is you can see the other option. See, download and language options. If you are from some other country and you want in different language apart from English, you can just click on this, see, download or language options.

So once I click on that, it open a new app and there you can see I can change the language. Okay you see I can change the language from here. I can choose whichever language I want and I can just click on download button here.

This will be a customizable download option. Before you download, you can see what are the system requirements for this particular software, you can see the installation instruction if something required and then you can click on download and installation. Click next button again adn again, go for installation. Once it is done, you can just search for power bi in the search bar, the software here will be restore, This will open the power bi software.

How to upgrade Power BI Desktop

Now, many of you might have the older user interface of power, So how do you upgrade the newer use interface? Power, BI updates, every month, not every year , every month new features are coming. How do we activate those features? When you compare your current power bi desktop software with current version, you can see for me the user interface is something different, you might have some different option here.

For upgrading, 

You can go to that file > Option and settings then click on options button here.

you can see, there is a preview features. Some option will find here as preview features, this one, this is the most important part preview features here. Whatever new features are been launched by Microsoft company.

Since Power BI new features are coming every month you have to select this checkbox and click on, okay? After you click on okay, you have to restart your our power bi software then only this new features will be installed. 

Same way if you are in the transform data, I’ll just click on transform data. This will open a power query editor, okay?

Now here also, same thing in power query, click file> options and settings > select options here. Also, you can see the same options will be visible for upgrading new features that is previous features here.

Also, you can add some new features once you select all, the checkboxes whichever are required. Not all suppose the Spanish language , if I don’t require the Spanish language so I can be select that and I can click on, okay, and click apply. So then it will ask you to close software and reopen that. So all the features will be installed. But I hope you understood how to download install and upgrade the new features of power bi here. 

I hope this article will be very helpfull for beginners, here i explained everything about power bi download installation and upgrade. As we already discussed , power bi new featues are relaeased every month, so once you installed the software, upgrade featuers every month.

Introduction

Currency conversion is a crucial aspect of financial management, especially for businesses operating in multiple countries. In Dynamics 365 Finance and Operations (D365FO), converting transaction currency amounts to the company ledger currency ensures accurate financial reporting and compliance. Let's dive into how you can achieve this seamlessly in D365FO. 

In Many situations an organization must do transaction with foreign currencies. Every ERP system has the option for converting Transaction Currency Amount to Company Ledger Currency.

Understanding Currency Conversion

Definition and Purpose

Currency conversion involves translating amounts from one currency to another using exchange rates. This is essential for businesses to consolidate their financial statements, manage risks, and comply with local regulations.

Key Terms and Concepts

Base Currency: The primary currency used by a company for its financial reports. The default currency, also known as the base currency, is set during the initial setup of the ledger.

Transaction Currency: The currency in which a transaction is initially recorded.

Exchange Rate: The rate at which one currency can be exchanged for another.

Setting Up Currencies in D365FO

Configuring Base and Transaction Currencies

In D365FO, you can set up both the base currency (company ledger currency) and transaction currencies. Navigate to the General ledger > Ledger setup > Ledger and specify the base currency.

Convert Transaction Currency to Company Ledger Currency using X++ Code

class CurrencyConverter
{
    public static void main(Args _args)
    {       
     CurrencyExchangeHelper currencyExchangeHelper;
     CurrencyCode transCurrencyCode = 'INR';
     AmountCur amountCur = 2500.00;
     AmountMst amountMST;       
     currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(),         systemDateGet());
     amountMST =
currencyExchangeHelper.calculateTransactionToAccounting(transCurrencyCode,
amountCur ,true);
     info(strFmt('Accounting
Currency Amount = %1',amountMST));
    }
}

This line declares a variable currencyExchangeHelper of type CurrencyExchangeHelper. This helper class is used to manage currency exchange operations in Dynamics 365 Finance and Operations (D365FO). 

Set Transaction Currency Code int 'INR', which stands for Indian Rupees. This variable specifies the currency of the transaction that needs to be converted.

Then set Transaction Amount amountCur variable is set to 2500.00. This represents the amount in the transaction currency (INR) that needs to be converted to the accounting currency.

Variable amountMST is declared to store the converted amount in the accounting currency (also known as the ledger currency).

Next line initializes the currencyExchangeHelper object. The newExchangeDate method takes two parameters: 

• Ledger::current(): This gets the current ledger, which is used to determine the base accounting currency. 
• systemDateGet(): This gets the current system date, which is used to fetch the relevant exchange rate.

Finally, the info function is used to display a message in the Infolog. The strFmt function formats the string by replacing %1 with the value of amountMST. This displays the converted amount in the accounting currency.

In the browser window you can see the output of the above code,

Conclusion

Converting transaction currency amounts to the company ledger currency in D365FO is vital for accurate financial reporting and compliance. By understanding the process, setting up the necessary configurations, and following best practices, businesses can ensure smooth and error-free currency conversions. D365FO can handle multiple exchange rates for different transaction types and periods. Best practices include maintaining accurate exchange rates, conducting regular audits, and using automated tools for updating rates.

Introduction

In this article I will be showing you what exactly Power BI and what are the advantages of using power bi and why you should choose power BI, let us see that one by one. I promise, if you’re ready to unlock the full potential of your data, Power BI is your go-to solution. Let’s embark on this journey together and harness the power of data-driven decision-making with Power BI.

Key Points 

Power BI is a business intelligence tool which will help you to analyse your data to clean your data and convert that data into visual formats.

Search volumn of Power BI ig High compared with Tableau.

You can create approximately 280 visuals different charts in power bi.

Costing part power bi is one of the cheapest tool in the Gartner Magic quadrant the compared with other bi tools.

Data connectivity power bi has a capability of importing data from 100 different data sources.

Power Bi is recognized by Gartner.

What is Power BI?

I can define power via in two ways. In a simple language Power BI is a business intelligence tool which will help you to analyse your data to clean your data and convert that data into visual formats. where you can create different reports and different dashboards.

In the other language if you ask me, I can say power bi is a collection of components the first one is a power query, power query is a ETL tool, which will extract transform and load the data or in other language, if I see, it is used for cleaning the data when you have blank records null values empty records or different unclean data, it will clean the data for visualization.

The second tool is we have Power Pivot, Power Pivot is used for data modelling. when u have multiple data sources if you want to connect them to create relationship between them, you can use power pivot.

The third tool is for visualization that is Power View, power view will help you to create 250 + charts. That can be bar chart or do nut chart or column chart. these different type of charts will be useful for presentation for reports and dashboards.

If You want to share the support with different people across the world, you can use power bi service. so Power BI service will help your users to communicate between each other via that reports. So it’s a package of four components power query, power pivot power view and power bi service.

Why should go for Power BI?

What are the reasons for jumping to Power BI, why not the other tools I can help with the top 5 reasons why you should go for Power BI

1. search volume in Google

There is one website named as Google Trends which will help you to find out interest over time for any search item. So I try to search for power bi in last five years across the world. How are people searching for power bi has it increased? or decreased so you can see? There are for power bi here it has an increasing format.

same way i try to compare with other tools like tableau , power Bi and tableau which one is growing in the market. Carefully we observe the red line is for tableau and the blue line is from power bi. the tableau line if it’s constant it’s not that much changing but power bi is increasing. So if you observe here then trend or the search volume for power bi is increasing as compared to others.

2. Maximum number of features

If you compare with other tools in the market. power bi has maximum number of features. You can write 1200 different functions m functions and DAX function. You can create approximately 280 visuals different charts in power bi so this features are not available in other tools. There are there, but they are not that much great or not much in quantity as compared to power bi.

3. Costing

Now the other reason you should go for Power bi is costing part power bi is one of the cheapest tool in the Gartner Magic quadrant the compared with other bi tools. Power bi is the cheapest it’s hardly $10 per user per month and if you try to purchase in bulk for many users the licence cost will come down.

4. Data Connectivity

Data connectivity power bi has a capability of importing data from 100 different data sources. You can bring data from Excel SQL oracle big data Hadoop. You name the data source and it can pull data from that data source. You can pull data from structure. Semi- structured and unstructured data sources in power bi, so it has a great capability in terms of data connectivity.

5. Recognized by Gartner

Now there’s one more reason why you should go for Power BI be in the fifth reason it is recognized by Gartner. Gartner is a company to compares all the bi products in the market, now in year 2024, it has created report where you can see Microsoft belongs to leader magic quadrant here. Now if you compared with all the bi products Microsoft is on the top , Microsoft is not only for power BI but it is a package of others also. so still it is the leaders quadrant here.

How Power BI works?

The last part is how power bi works? let us consider power bi as a package and i can divide power bi into two parts, Power bi a desktop and Power bi a services.

1. Power BI Desktop

Power bi a desktop is a developer tool is a desktop application download and install, the power based service is online version it’s a cloud version which is used for sharing and collaboration. Now suppose if you want to create some dashboards and reports first of all, you need some data, so power bi can pull data from Excel , MS Access, share point, outlook , PDF or website etc. so you can bring data from any number of data sources all together or one by one.

2. Power BI Service

After doing that you can eat a modelling and also you can work on creating different charts and maps. After creating this reports you want to focus on publishing the report on online service. So from here, you can just publish to power bi service account.

After publishing, you want to share with different people sitting across the world, so from power bi service you can share the reports with different people, departments, organizations or individuals. So this is the complete architecture of power bi.

Now the best part about this power bi I can say is, it is completely automated you do not have to do anything manually, so once you teach power bi that will data from so and so data source clean data in this proper manner and then send reports, this complete architecture will be done automatically. So that is the best part about power bi and therefore is known as business intelligence. I hope you have understood. What is power bi? Why you should learn power bi and how we works?

In Dynamics 365 for Finance and Operations (D365FO), handling time-sensitive data is crucial for efficient business operations. Often, extracting precise time information from DateTime fields becomes necessary for various tasks like reporting, analysis, and data processing. In this blog post, we'll explore a simple method to extract time information in seconds from DateTime fields within D365FO.

Dynamics 365 offers a powerful development platform with X++ as its primary programming language. Leveraging X++, we can easily access and manipulate DateTime fields to extract specific time components.

To extract time information in seconds from a DateTime field, we utilize a straightforward approach:

Output

Joins in Dynamics 365 Finance and Operations typically refers to the process of combining data from two or more related entities in a query or a report. These joins are commonly used in retrieving data from multiple tables or entities within the Dynamics 365 system.

The primary purpose of using joins in Dynamics 365 is to fetch related data across different entities or tables in a single query. This allows users to gather comprehensive information that spans multiple aspects of their business processes.

For example, if you're working with Dynamics 365 Finance and Operations, you might want to retrieve information about customers and their associated orders. In this scenario, you would use a join operation to combine data from the "Customer" entity/table and the "Order" entity/table based on their common fields such as customer ID.

By utilizing joins, users can create reports, dashboards, or perform complex queries that involve data from various parts of the Dynamics 365 system. This capability enhances the ability to analyse relationships and dependencies between different entities, providing a more holistic view of business operations.

T- SQL Joins

Any code associated with database is written in X++ will end up running a SQL statement against the database. So before learning D365 Joins you must have a look in to SQL Joins,

In T- SQL we have the following joins,
Inner Join – returns row when there is a match in both tables.
Left Join – returns all rows from the left table, even if there are no matches in the right table.
Right Join – returns all rows from the right table even if there are no matches in the left table.
Full Outer Join – returns all rows when there is a match in one of the tables.
Self-Join – used to join a table to itself, as if the table were two tables.
Cross Join – is a type of join operation that combines each row from one table with every row from another table.

D365 X++ Joins

In Dynamics 365 Finance and Operations, X++ joins really support only four type of joins, developer can write x++ codes very similiter to T-SQL code to retrieve the data. I will explain the four types of joins and its similarity to T-SQL joins,

• Join - Join keyword in X++ is used to returns row when there is a match in both tables. Which is same as inner join in T-SQL, but only one thing is keyword “inner” is not used with X++ join. But the effect is same, which will retrieve a row when there is a match in both tables.
• Outer Join – this will returns all rows from the left table, even if there are no matches in the right table. Which is same as Left join in T-SQL,

Note: There is not right join in x++. However, a developer can just change which table is first, and which table is second to come up with the same result as a right join.

• Exists join – this is another type of join, which is like Join in X++, but only the difference is, it will only retrieve only one number of records from the first table even though more than one match found in the second table. if there are many matching records in the second table, the number of rows in the result set will not increase. The system will stop looking after it finds one match in the second table. 

Note: don’t confuse the keyword Exists with T-SQL, in T-SQL Exists keyword is used for another purpose.

• NotExists join – the keyword NotExists join is used to retrieve a row in the first table if there does not exist a match in the second table.

In the next article we willexplain each and every joins in dynamics 365 fo in detail with examples. if this article is usefull, try to share to your friends and colllegues.

• Assess Your Needs: Begin by identifying your business's specific needs. What processes require automation? Involve key stakeholders to gain insights into the pain points and inefficiencies within your organization.
• Budget Considerations: Determine your budget for ERP implementation, including software, hardware, training, and ongoing maintenance costs. Be realistic about what you can afford.
• Scalability: Choose a system that can grow with your startup. Your ERP should adapt to changing business demands and expanding operations without the need for a complete overhaul.
• Industry Requirements: Some industries have unique compliance and operational requirements. Ensure that the ERP system you choose can accommodate these specific needs.
• Functionality: Evaluate the functionality of ERP options. Does it cover all the critical aspects of your business, such as finance, inventory management, HR, and customer relationship management (CRM)? Make a checklist of essential features.
• User-Friendliness: An ERP system should be user-friendly, as it will be used by employees across various departments. Complex systems may require extensive training, which can be time-consuming and costly.
• Vendor Reputation: Research ERP vendors thoroughly. Look for their track record, customer reviews, and industry reputation. A reliable vendor is more likely to provide continuous support and updates.
• Customer Support: Excellent customer support is vital. Confirm that the vendor offers ongoing support, and inquire about response times and available channels for assistance.
• Integration Capabilities: Ensure that the ERP system can seamlessly integrate with your existing software and systems. This reduces the risk of data silos and enhances data flow.
• Security and Compliance: Data security is paramount. Verify that the ERP system complies with industry regulations and has robust security measures in place to protect sensitive information.

• Customization: The ability to tailor the ERP system to your startup's unique needs can be a significant advantage. Look for customization options.
• Demo and Testing: Request demos of the ERP systems you're considering. This hands-on experience will help you gauge how well the system aligns with your requirements.
• References: Reach out to other businesses that have implemented the ERP system. Their experiences can provide valuable insights into the system's real-world performance.
• Total Cost of Ownership: Consider the long-term costs associated with the ERP system, including licensing, maintenance, and potential expansion expenses. Calculate the total cost of ownership over several years.
• Implementation Plan: Develop a comprehensive implementation plan that outlines the steps, timeline, and responsibilities for deploying the ERP system in your startup.
• Change Management: Prepare your team for the transition to the new ERP system. Adequate training and change management strategies are essential to minimize disruptions.
• Continuous Improvement: Finally, remember that selecting an ERP system is not the end of the journey. Regularly assess and optimize the system to ensure it continues to meet your startup's evolving needs.

Conclusion

Dr.Umesh Pandit

Dr.Umesh Pandit expertise in Dynamics 365 for Finance and Operation Administration and Troubleshooting, Microsoft Dynamics AX Infrastructure Support, Dynamics 365 operations on-premise (D365),Microsoft Dynamics 365 Admin,Axapta Administrator,AX Administrator,Microsoft Dynamics AX Deployment, Azure,DMF, Excel Add-In, Ware House, AX Mobile apps, Google Cloud, Microsoft Dynamics ISV,Management Reporter 2012 for Microsoft Dynamics ERP, Microsoft SQL, Microsoft Windows Server, SSRS, TFS, POS, Retail, CRM, Microsoft SharePoint, Hyper-V and System Center. Specialist in Azure, Microsoft Dynamics AX Infrastructure Support, Microsoft Dynamics AX Deployment, Implementations, Upgrades, Migrations, Installation, End User Training, User Training, IT/Admin Training, Pre, and Post Go-Live Support activities.

Are you new to Power BI and you want to try it out, as a fresher getting a perfect dataset is little difficult, but here Microsoft provides a sample dataset which is already clean, and you can easily use to develop your power bi dashboard reports. The sample is dataset is embedded with Power BI desktop version.

1. Where Sample Dataset Located?

You can easily access the sample dataset by using the path.

Open Power BI desktop version and open the menu Help > Examples > Sample dataset.

2. Load Sample dataset

The first step of dashboard development is, we need to load the dataset to Power BI desktop, for this click on the sample dataset from the path shown above and click Load Samples Data button as shown below.

Our sample dataset contains two data sheets, tick financials sheet, then you can see the data loaded in the preview window, click the Load button. Since the data is already clean, we no need to clean the data again, so we can directly loan the data from dataset to the power desktop.

Once the data fully loaded, you can see one table which is created in the right side of the window under the Data Section as shown below.

3. Create a Date Table

Our sample dataset contains sales related data, so we need to add different search criteria like filter by date period, filter by customer name, filter by nationality etc… the recommended method for filtering with date period is use a date table and map this date table to required table where the data is stored by using the model view window.

Once you created a date table, next step is you need to make a relationship with financial table and date table,

For this, you need to open the model view which is located at the left side of the power bi desktop window, then make a one-to-many relation with date field of date table to the date table of the financial table as shown below.

4. Ad visuals

Once you have completed all the above basic steps, we need to add visuals to the power bi project, here we are adding mainly Card Visuals to show the summary data, Slicers for various filters, donut chart, Column Charts and different bar charts.

1. Slicer

A slicer is a visual filter used to filter the dashboard and display the information only we want. In our project we need to filter the dashboard reports by date, customer name, location, product etc.

For this open the report view tab from the left side and then add four slicer visuals from the visualization tab, these four slicers are used to filter the report with year , month , product and country, then right click on the year slicer and then drag and drop the year column of the date table to the field property of the year slicer , similarly right click on the month slicer and then drag and drop the “Month Name” column of the date table to the field property of the slicer Month as shown below.

For adding Product and Country, drag and drop the fields from financials table, now the slicer set up is ready,

In Visualization Tab, click on the format your visuals > General, the change the Title of the slicer as shown in the figure.

you can see the four slicers are allocated at the top of the dashboard report.

2. Add Card Visuals

In our project we use card visuals to display the summary of total sales, total costs, profit, and the total number of items sold.

For this add four card visuals from visualization tab,

• Right click on the first card visual and then drag and drop the “Sales” column of the date table to the field property of the slicer Month.

• Right click on the second card visual and then drag and drop the “COGS” column of the date table to the field property of the slicer Month.

• Right click on the third card visual and then drag and drop the “Profit” column of the date table to the field property of the slicer Month.

• Right click on the fourth card visual and then drag and drop the “Units Sold” column of the date table to the field property of the slicer Month.

In Visualization Tab, click on the format your visuals > General, then change the Title of the card visuals as shown in the figure.

you can see the four cards are allocated at the top of the dashboard report.

3. Clustered Column Chart

A clustered column chart is used to create a sale by month report, X – axis of the chart will be “MonthName”, and Y-Axis of the chart will be the “Sum of Sales”,

For this add a clustered column chart, the right-click on the chart, drag the “MonthName” column of the financial table to the X-axis of the charts, similarly drag the “Sales” column of the financial table to the Y-axis of the charts.

In Visualization Tab, click on the format your visuals > General, then change the Title of the Clustered Column Chart visuals as shown in the figure.

you can see the clustered column chart is allocated as the first chart in the dashboard report.

4. Clustered Bar Chart

A clustered bar chart is used to create a sale by segment report, X – axis of the chart will be “Sales” and Y-Axis of the chart will be the “Segment”,

For this add a clustered bar chart, the right-click on the chart, drag the “Sales” column of the financial table to the X-axis of the charts, similarly drag the “Segment” column of the financial table to the Y-axis of the charts.

In Visualization Tab, click on the format your visuals > General, then change the Title of the Clustered Column Chart visuals as shown in the figure.

you can see the clustered bar chart is allocated as the second chart in the dashboard report.

5. Donut Chart

A Donut chart is used to create a sale by country report, “Legend” of the chart will be “Country” and “Values” of the chart will be the “Sales”,

For this add a Donut chart, the right-click on the chart, drag the “Country” column of the financial table to the “Legend” of the charts, similarly drag the “Sales” column of the financial table to the “Values” of the charts.

In Visualization Tab, click on the format your visuals > General, then change the Title of the Clustered Column Chart visuals as shown in the figure.

you can see the donut chart is allocated as the third chart in the dashboard report.

6. Stacked Column Chart

A stacked column chart is used to create a sale, cost, and profit report by month report in a single visual, X – axis of the chart will be “MonthName”, and Y-Axis of the chart will be the “Sum of Sales”, “Sum of COGS” and “Sum of Profit”,

For this add a clustered column chart, the right-click on the chart, drag the “MonthName” column of the date table to the X-axis of the charts, similarly drag the “Sales”, “COGS” and “Profit” columns of the financial table to the Y-axis of the charts.

In Visualization Tab, click on the format your visuals > General, then change the Title of the Clustered Column Chart visuals as shown in the figure.

you can see the stacked column chart is allocated as the second chart in the dashboard report.

Finally you can see the output of our sales dashboard project as shown below,

As discussed above here we have four slicers for filtering the charts  with different search criterias, you can choose four slicers values according to your requirement , when we choose the slicer values , you can see the values in the charts and cards will be chaging according the value we filtered in the slicers.

for example , in the month slicer, choose january only , the n the charts will be only shown the data for january only.

Conclusion

Now our project is over, this project is an easy project, because our sample financial dataset is already clean, also we have only two data models here, for more complex projects we may have more data models and need to clean the all the tables also need to make different relation ship to various  models. So as a fresher you can start power bi learning by using this simple Microsoft sample data set.

Useful Tags

How do you Analyse sales data in Power BI?
How do I create a sales report in Power BI?
Does Power BI have dashboard templates?
How do I create a Power BI dashboard example?
Sales Analysis Power BI Dashboard Example
Sales and Marketing sample for Power BI
Top 18 Microsoft Power BI Dashboard Examples 2024
Complete Excellence: Power BI Sales Dashboard
Power bi sample sales kpi template
power bi sales dashboard templates free download
power bi dashboard templates free download pbix
power bi sales report example
sample sales data excel for power bi
sample sales data for power bi
power bi sales dashboard examples
power bi dashboard examples pbix