Software + Imagination
We can also perform time intelligent functions on our date fields in DAX. Let’s compare this year’s sales to last year’s sales. We will create a new measure for previous year sales. First, let’s create a date table. This is created in Power BI Query Language (M): Next, create the measure. We are calculating the sum of the extended price, in the previous year, using the PREVIOUSYEAR function: What this means, is … Continue reading Using DAX PREVIOUSYEAR
Some DAX functions will return a single date. Let’s go through some of these with our dataset. STARTOFMONTH If we apply this to our data without a filter, it will find the start of the first month: LASTDATE ENDOFQUARTER Applying this to 2016, it uses the first 2016 record (09/02/2016) and gets the end of that quarter:
Let’s start with a dataset that has sales orders. There are sales order lines: And the data looks like this: However, there is no extended price in this dataset. To calculate it, let’s add a new calculated column. Click on the ellipse on the dataset: And select New column: Add the formula: The new column will appear in the dataset: And the new column can be added to our Power BI … Continue reading DAX Calculated Columns
DAX, or Data Analysis Expressions, is a library of functions, formula and query language used in analytics. It is used in: Power BI SSAS Tabular models (SQL Server Analysis Services) Power Pivot in Excel To write DAX formulas in Power BI, open Power BI Desktop and go to your dataset. In the example below we have Transactions: Click on the Ellipse next to the dataset and you will see “New measure” … Continue reading Introduction to DAX (Data Analysis Expressions)
DAX has various date functions we can use to help us visualize data. Let’s go through an example. In our dataset, we have order lines and we have the orders associated with them. Orders have a date field, i.e. when our customer placed the order: We have an active relationship in Power BI matching our orders and lines: We can derive the year, month, day, hour, minute and second from … Continue reading Using DAX YEAR
In Power BI, select Manage Gateways: Select Add data sources to use the gateway: Give the name as Excel Spreadsheet and the type as File: Provide the path to the file and also the windows username and password: You can add multiple data sources by selecting Add Data Source:
To use the Power BI Gateway, run the executable after downloading from Power BI: You will see this window below: Click Next: Enter email address to sign in: Add gateway name and key: Gateway installed: Service settings: Diagnostics: Network: Now in PowerBI.com, go to Manage Gateways: You can now see the new gateway: Note the differences between the Personal Gateway and the On-Premise Data Gateway: Personal gateway On-premises data gateway Cloud services it … Continue reading Power BI Gateway Installation
There is a function called StartOfWeek that displays the start of the week in Power Query formula language. Syntax: Date.StartOfWeek(<date>) The week starts on Sunday and runs through to Saturday. To use Monday to Friday, you can add a day to it using the function Date.AddDays(<date>, 1).
When you log into Power BI, you are presented with a dashboard before you select any of the other dashboards. This is your featured dashboard. If you have not set a featured dashboard before, it will default to another dashboard. To set a featured dashboard, do the following. First, select the dashboard you would like to see when you log in, and click on the ellipse on the top right: Next, select “Set … Continue reading Power BI Featured Dashboard
Power BI has a RELATED function that allows you to get data from a related table. Let’s go through an example. We have 2 tables: Goals, which holds salesperson’s goals Sales, which holds sales transactions The tables have a relationship of the common field Salesperson. Now, let’s say we want a field in the Sales table which is the Goal field from the Goals table. To do this, create a … Continue reading Power BI – DAX – RELATED