cancel
Showing results for 
Search instead for 
Did you mean: 

Creating Complex Reports using DAX in Power BI Desktop Tool

 

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:

 

1.png

                                                         Fig: Modified Sales

 

 2.png

                                                         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],

    42,"Bangalore",

    51,"Rajasthan",

    52,"Tamil Nadu",

    53,"USA",

    54,"Gujrat",

    56,"Mumbai",

    57,"Pune",

    "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.

 

3.png

                                                           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.

 

4.png

                                                            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.

 

5.png

                                                  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.

 

11.png

                                    Fig: Running Total Comparison without filter

 

12.png

                                        Fig: Running Total Comparison with filter

 

Conclusion

 

It is possible to leverage DAX functions available in Power BI to calculate and comapre data required for reporting purposes and data analysis.

Comments

Excellent example, only thing I would have used was a date table which would eliminate some of the Dax calcs or changed them but this looks good. Nice to see more people using this tool. 

Thank you @kzmanus for your suggestion !

Another method for returning the Week day name or the month name is to use the =Format formula.  For example to get the day of the week you would use =Format([Date],"DDDD") . "DDDD" will return the long name of the weekday such as Monday.  "DDD" will return the short day of the week name i.e. Mon .  You can also use the same formula to get the Month name .  =Format([Date],"MMMM") . Again "MMMM" will return the long month name and "MMM" will return the short name.

it is very nice for business intellegence.....thank you all microsoft team.... who developed this.

Very concise and beautifully crafted example. Thanks for the insight. To give some flexibility to the model, instead of a fixed exchange rate one can provide a table with single column of few exchange rates. The Amount will then be dependent on the user selection of applicable rate at the time of report. This table is not going to be connectd to the data model.

 

I think the use of disconnected table is the only way to change the value of paramters inside formuals andmeasures, as there is no TextBox control whatsover to allow for the user interaction. Am I right?   

 

Hi, i think my requirement is similar to yours, view link and kindly comments;

 

https://community.powerbi.com/t5/Desktop/How-to-create-Year-Month-Date-hierarchy-while-creating-repo...

 

Very nice post. If these data are dummy, then is it possible to post the excel sheet also. So, that person like me who are new to Power BI  can do an hands on.

that was a realy nice information. can you please share this sample excelsheet to this mail jayavardhan.pbi@gmail.com