12 Jan’16

BIComplex Power BI Reports using DAX Functions


Power BI Desktop Tool and knowledge on DAX functions

Purpose of the Setup:

To learn how we can leverage DAX functions to create complex Power BI reports.

Here we will be looking at different DAX functions for the below mentioned reports in Power BI;

  1. Calculate and compare Sales Figure by Day
  2. Calculate and compare Sales Figure by Date
  3. Calculate and compare Sales Running Total

Source File Details:

To demonstrate this concept, we have created an excel file with dummy data and connected it to Power BI Desktop Tool.

In the Excel source file, we have created few sheets along with sample data as shown below:

Modified Sales
Fig: Modified Sales
Running Total
Fig: Running Total

Sales by Day Report

To get the comparison of sales figure by date for previous and current month, we need to use below DAX functions for creating calculated columns.

Step 1:

Todays Date = TODAY()-1

To calculate yesterday’s date.

Step 2:

DateMonth = MONTH (Modified_Sales[Date])

To calculate month of sales date.

Step 3:

Today Month = MONTH (Modified_Sales[Todays Date])

To calculate month of yesterday’s date.

Step 4:

Previous Month = IF (Modified_Sales[Today Month] =1, 12,Modified_Sales[Today Month]-1)

This is to calculate the Previous month dynamically, where the DAX function states that if the current month is 1 then the previous month will be 12 or else for all other months is will automatically calculate the current month minus one as the previous month.

Step 5:

Day = DAY (Modified_Sales[Date])

This is to calculate which day of sales date.

Step 6:

City = SWITCH ([WhsCode],
    52,"Tamil Nadu",
    "Invalid City Code"

Above DAX function is to derive warehouse name based on warehouse code.

Step 7:

WeekDay = Weekday (Modified_Sales[Date],2)

This weekday column will display the week day in numeric considering Monday as 1.

Step 8:

Day Name = switch (Modified_Sales[WeekDay], 1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thrusday",5,"Friday",6,"Saturday",7,"Sunday")

To derive the week days in words using switch function.

Step 9:

Week Number = weeknum (Modified_Sales[Date],2)

This will calculate which week of the year is it.

Step 10:

MonthWeek = if(Modified_Sales[WeekDay] >= 1 && Modified_Sales[WeekDay] <=7 && Modified_Sales[Day] >= 1 && Modified_Sales[Day] <= 7,"1",if(Modified_Sales[WeekDay] >= 1 && Modified_Sales[WeekDay] <=7 && Modified_Sales[Day] >= 2 && Modified_Sales[Day] <= 14,"2",if(Modified_Sales[WeekDay] >= 1 && Modified_Sales[WeekDay] <=7 && Modified_Sales[Day] >= 8 && Modified_Sales[Day] <= 21,"3",if(Modified_Sales[WeekDay] >= 1 && Modified_Sales[WeekDay] <=7 && Modified_Sales[Day] >= 15 && Modified_Sales[Day] <= 28,"4","5") )))

This will calculate the month’s week number.

Step 11:

Month Week Name = Modified_Sales [MonthWeek] & "-"&Modified_Sales [Day Name]

To calculate month week with day name.

Step 12:

Amount = if (Modified_Sales[City] = "USA",Modified_Sales[NetAmount]*64.80,Modified_Sales[NetAmount])

This will calculate the Amount in rupees and convert the USA currency into Rupees, thus all the amount will be in one currency.

Step 13:

Create Measures;

Previous Month Total Sales = CALCULATE (SUM (Modified_Sales[Amount]), FILTER(Modified_Sales, Modified_Sales[DateMonth]=Modified_Sales[Previous Month]))

This is to calculate the Total Sales in Previous month.

Step 14:

Current Month Total Sales = CALCULATE (SUM (Modified_Sales[Amount]), filter(Modified_Sales,Modified_Sales[DateMonth] = Modified_Sales[Today Month]))

This is to calculate the Total Sales in Current month.

Below report compares sales figure by weekday of previous and current month. For eg. 1st Monday of previous month vs 1st Monday of current month.

Sales By Day
Fig: Sales by Day
Below report compares sales figure by date of previous and current month. For eg. 1st day of previous month vs 1st day of current month.
Sales by Date
Fig: Sales by Date


Running Total Report

To get the sales running total report, which can be filtered on the basis of warehouse, Team manager & Beat level.

Data Model Relationship
Fig: Data Model Relationship

Step 1:

WhsCode = Related (Warehouse[whscode])

This will get the Warehouse code with relation to the warehouse code in the table Warehouse.

Step 2:

MY = Running_Total[Yr ] & Running_Total[MonT]

This will calculate and display the Year and Month together in numeric value.

Step 3:

MonYr = Running_Total[Month] &" "& Running_Total[Yr]

This will display the month with first 3 letter abbreviation and year in 4 digit format.

Step 4:

Month = SWITCH (Running_Total[Mn], 1, "Jan",2,"Feb",3,"Mar",4,"Apr",5,"May",6,"Jun",7,"Jul",8,"Aug",9,"Sep",10,"Oct",11,"Nov",12,"Dec")

This will display the month in the first three letter abbreviation format.

Step 5:

MonT = SWITCH (Running_Total[Mn],1,"01",2,"02",3,"03",4,"04",5,"05",6,"06",7,"07",8,"08",9,"09",10,"10",11,"11",12,"12")

This will display the month in numeric value.

Step 6:

Create Measure;

Month Amount = calculate(sum(Running_Total[Amount]),filter(all(Running_Total[Dy],Running_Total[WHScode]),Running_Total[Dy] <= max(Running_Total[Dy])))

This will calculate the running total from total amount.

Running Total Comparison without filter
Fig: Running Total Comparison without filter
Running Total Comparison with filter
Fig: Running Total Comparison with filter


Written by

Team Member


4 thoughts on “Complex Power BI Reports using DAX Functions”

  1. Hi,

    I created the dummy data and imported it in Power BI desktop but, i am not able to use the date column in measure calculations. Can you please let me know the reason?

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Reading more then 10K records in D3FOE OData API

    11 July’ 2018

    Introduction: We all know Dynamics 365 Finance and Operations has limitation of 10K records to be fetched at a time usi...

    Read more
  • Paging in D365 Customer Engagement v9.0

    10 July’ 2018

    Introduction: The Xrm.retrieveMultipleRecords method is used to retrieve a collection of records in Dynamics 365 Custom...

    Read more
  • Set up Dynamics 365 connection in Microsoft Social Engagement

    10 July’ 2018

    Introduction: This blog explains how to Set up Dynamics 365 connection in Microsoft Social Engagement. Steps to be follo...

    Read more
  • Voice of the Customer failed to install

    10 July’ 2018

    Introduction: Many people face issues in installing Voice of Customer solution on v9 environment and trying repeatedly ...

    Read more
  • Scribe Insight AX as a Web Service Find Block issue

    10 July’ 2018

    Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: Basi...

    Read more