-
How to correctly calculate Today using DAX
Colin Maitland, 26 September 2024
I often want to calculate and use date ranges that are relative to today in various Power BI Online reports. However, there is no DAX function that calculates the correct date considering the time zone and daylight-saving time when the time zone is something other than UTC.In this article I will dem
Read the full article here -
How to configure the dates display format in Power BI reports
Colin Maitland, 18 May 2023
Have you ever looked at a Power BI report and seen that the dates are not in the format used by the country in which you reside or work, such as 01/13/2023 instead of 13/01/2023?In this article, I will demonstrate how to configure date fields in a Power BI report to be displayed for all users so tha
Read the full article here -
How to add a Power BI report or dashboard to Dynamics 365
Colin Maitland, 18 May 2023
The following is the first page of a Power BI report that I want to add to a Dashboard in Dynamics 365 Model Driven app. In this article, I demonstrate how to do this. The same process can be used to add a Power BI dashboard to a Dashboard in a Dynamics 365 Model-Driven app.The process consists of n
Read the full article here -
How to calculate the percentage of a selected group of customers in Power BI
Colin Maitland, 12 April 2023
In this article, I will demonstrate how to calculate the percentage of the total for selected Customers versus the percentage of the total for all Customers.For example, I want to use a slicer to select the Top 10 Customers and for each of those display what percent the Actual Revenue is compared wi
Read the full article here -
How to add sequential row numbers to a table using a SQL CTE
Colin Maitland, 23 February 2023
In this article, I will demonstrate how to add a unique sequential row number to records in a SQL Server table using a SQL Common Table Expression (CTE).This is an example of selected columns from a table named Opportunity. This list of Opportunities selected here has been sorted by the Created On d
Read the full article here -
How to use a calculation group to change a relationship in Power BI
Colin Maitland, 23 February 2023
In Power BI, Calculation Groups act as a filter that modifies the measures they are applied to. The use of a Calculation Group reduces the number of measures that need to be written to support multiple calculation scenarios.In this article, I demonstrate how a Calculation Group may be used to change
Read the full article here -
Using Power Query to create a Date table
Colin Maitland, 14 October 2022
In this article, I will demonstrate how to use Power Query in an application such as Microsoft Excel or Power BI Desktop to create a Date table.When creating a data model, it is common to have a Date table for use in measures and visualisations. This is particularly important for Date/Time Intellige
Read the full article here -
Power BI - P2 - Calculate Change vs Difference for two Date Ranges
Colin Maitland, 19 August 2022
This is the second part of a two-part article in which I demonstrate how to structure the Power BI data model and write measures to calculate and display the change and difference between two user selected sets of dates for a measure.In the first article, I demonstrated the configuration of the data
Read the full article here -
Power BI - P1 - Calculate Change vs Difference for two Date Ranges
Colin Maitland, 26 July 2022
This is the first part of a two-part article in which I demonstrate how to structure the Power BI data model and write measures to calculate and display the change and the difference between two user selected sets of dates for a measure such as Actual Revenue for Won Opportunities.The image below sh
Read the full article here -
Power Query - Conditionally Replace Values in a Column with Values from another Column
Colin Maitland, 27 May 2022
In this article, I will demonstrate how to use Power Query to conditionally replace values in a column with values from another column. We want to do this with the least number of steps, while handling blank values and maintaining the data type of the column for which values are to be replaced. For
Read the full article here -
Calculate Mean, Median, Mode and Percentile for a Measure in Power BI
Colin Maitland, 22 May 2022
In this article, I will demonstrate how to calculate the Minimum, Maximum, Mean, Median, Mode and Percentile for a set of values calculated using a measure. This demonstration considers whether the rows for which the measure returns a blank result are included or excluded. I will also demonstrate th
Read the full article here -
How to configure a Top X Slicer in Power BI
Colin Maitland, 13 May 2022
In this article, I will demonstrate how to configure a slicer and related visualisation, such as a table or chart, to dynamically select and display the Top X Customers based on a measure such as Won Revenue in a Power BI report. The pattern demonstrated in this article can be adapted to suit your b
Read the full article here -
Power BI - Calculating Mean and Actual or Percentage difference from Mean for a Measure using DAX
Colin Maitland, 10 May 2022
In this article, I will demonstrate how to use DAX to calculate the Mean, Actual Difference from Mean and Percentage Difference from Mean for a set of values calculated by a measure in Power BI. The Mean is the average of a set of numbers. That is, the sum of the values divided by the number of valu
Read the full article here -
Analysing Donor and Supporter dates in Power BI
Colin Maitland, 27 April 2022
In this article, I will demonstrate an approach that may be used in Power BI to analyse the dates such as Created On, First Donation and Last Donation dates for Donors or Supporters. This approach may also be considered for other scenarios, such as First Sale and Last Sale dates for Customers, First
Read the full article here -
Power BI - How to document a custom Power Query function
Colin Maitland, 17 April 2022
In a previous article, I demonstrated the configuration of a Custom Power BI UTC to Local Date/Time with Daylight Saving Time Function named UTCDateTimeZoneToLocalDateTime. Power Query can use this custom M function in a Power BI data model connected to Dataverse to transform UTC Date/Time/Zone data
Read the full article here
Category List
Filter by Author
Adam Murchison
Alfwyn Jordan
Arthur Mandisodza
Calum Jacobs
Colin Maitland
David Mochrie
Dominic Liu
Gayan Perera
Harshani Perera
Isaac Stephens
Jaime Smith
Jared Johnson
John Barrencechea
John Eccles
Lauren Withers
Paul Nieuwelaar
Roshan Mehta
Roz Millar
Ryan Blaikie
Ryan Ingram
Sarah Coleman
Sean Roque
Shalane Williams
