DAX formula to obtain the count from SCD type-2 dimension records

Data source used for this post is the ‘Reseller Sales’ data mart of the ‘AdventureWorksDW2017´ SQL server data warehouse. The ‘DimEmployee’ table in the data mart is clearly a SCD type-2 dimension, which means any update to the employee record is created as a new row with a new start date and the old employee … Continue reading DAX formula to obtain the count from SCD type-2 dimension records →

Customer retention over the years using DAX in Power BI

Data source used for this post is the ‘Internet Sales’ data mart of the ‘AdventureWorksDW2017´ SQL server database. In this post, we will write and use DAX formulas to get the customer retention count when compared with last year and when compared with all the previous years. In addition, we will also look at testing … Continue reading Customer retention over the years using DAX in Power BI →

Ranking using DAX in Power BI

Data source used for this post is the ‘Internet Sales’ data mart of the ‘AdventureWorksDW2017´ SQL server database. In this example, let’s look at ranking Countries and States individually according to the sales and then ranking Country and State fields when grouped together. For ranking, RANKX function is being used. RANKX function helps to provide … Continue reading Ranking using DAX in Power BI →

Color-code Visualizations using Grouping in Power BI

Data source used for this post is the ‘Internet Sales’ data mart of the ‘AdventureWorksDW2017´ SQL server database. Grouping clearly helps to better visualize and analyze our data. In this example, we will look at grouping the field values and use them to format the color of the visualizations. Let’s look at the steps involved … Continue reading Color-code Visualizations using Grouping in Power BI →

Dealing multiple date relationships with the fact table in Power BI

Data source used for this post is the ‘Internet Sales’ data mart of the ‘AdventureWorksDW2017´ SQL server database. During the data modelling in Power BI, although one could create multiple relationships between two tables, enabling a second relationship as ‘Active’ raises an error. Therefore, it could become difficult to measure the fact data in terms … Continue reading Dealing multiple date relationships with the fact table in Power BI →

DAX formulas to calculate MTD/QTD/YTD values and comparing them against same period last year MTD/QTD/YTD values in Power BI

Data source used for this post is the ‘Internet Sales’ data mart of the ‘AdventureWorksDW2017´ SQL server database. DAX formulas to calculate the month-to-date sales amount are below. You can use one of the below two formulas and create the MTD measure in the fact table where the actual measure exists: MTDSalesAmount1 = IF( HASONEFILTER(DateDimension[Date]), … Continue reading DAX formulas to calculate MTD/QTD/YTD values and comparing them against same period last year MTD/QTD/YTD values in Power BI →

DAX formulas to dynamically identify the last x Days/Months/Years, and using these formulas in Power BI

Below are the sample DAX queries that helps us identify the last 10 days from today’s date (including), last 6 months from current month (including) and last 5 years from the current year (including). 'Calendar Date'[Date] field in the below formulas is the 'Date' field in the date dimension. DAX formula to identify the last … Continue reading DAX formulas to dynamically identify the last x Days/Months/Years, and using these formulas in Power BI →