pivotronics

The origination of a lot of business reports happens in Microsoft Excel. When these reports attract wider acceptability in the organization or when the volume/velocity/frequency/processing complexity of these reports go beyond the capabilities of Excel, they will be expected to be moved to a more enterprise class platform like Power BI. However, you will get to see a lot of business users insisting on the look and feel of these reports to be retained the same way even after moving them to Power BI. Though this sounds like a reasonable expectation, there would be instances where it turns out to be a challenge to retain the look and feel of the Excel based report in Power BI.

Example: Though I was working on a lot more complex scenario, here I am sharing a simplified example to ease the understanding. Here is a report showing the day-wise production in units for all the dates in a month for each project, preceded with two calculations “MTD” and “YTD”. Also the days here dynamically change based on the current month.

Requirement: The requirement is to retain the same look and feel of the report when moved to Power BI.

Challenge: We need to use matrix visual in Power BI to display the dynamically generated dates like in the above screenshot. Also, in order to display the dynamically generated dates of the current month along with two textual values “MTD” and “YTD” preceding these dates, we need to have all such dates and these two textual values in one column. so that, when that column is placed in columns section of the matrix visual, they get displayed in the desired manner. But this means such column has to be a textual column and that further implies we cannot rely on standard date dimension table to show the production units for each of the dates and to show the calculations for “MTD” and “YTD”. So the challenge is how do we display these two textual values “MTD”, “YTD”, and the dates of the current month in proper order (oldest date to newest date) and then also perform calculations correctly without relying on the date dimension table?

Note that “Matrix” visual in Power BI is similar to “Pivot Table” in Excel.

Example: For simplicity, the data in the fact table (named Production) and the date dimension table (named Calendar) looks like the below in data view in Power BI:

The data model looks as below with a one-to-many relationship between “Date” column of “Calendar” table and “Production Date” column of “Production” table and a single directional filter flow direction from “Calendar” table to “Production” table.

Solution: Create a new calculated table that will have three columns:

• A column with all the dates of the selected month in text format and two textual values “MTD” and “YTD”
• A helper column that determines the sequencing order of the values in the above column
• Another helper column with all the dates of the selected month in date format and two values in date format representing “MTD” and “YTD”.

Here is the DAX to achieve the above defined new calculated table named “Custom Calendar”, with comments explaining what each line of the DAX code does:

Custom Calendar = VAR CurrentMonth = FORMAT(TODAY(), "Mmm-YY") //Read the system current month
RETURN UNION(SELECTCOLUMNS(FILTER('Calendar'
                                  , 'Calendar'[Month] = CurrentMonth
                                 ) //Pick up the dates of current month from standard date dimension table ("Calendar" table in this example)
                        , "Display Value", FORMAT([Date], "D-Mmm-YY") //add a column that displays dates of current month in user desired format
                        , "Date Value", [Date] //show the dates of the current month as is
                        , "Sort Key", INT(FORMAT('Calendar'[Date], "d")) //add a column that contains the value of day in the month from date column. This is to use it for sorting the"Display Value" column
                        )
            , {("MTD", INT(FORMAT(TODAY(), "YYYYMM")), -1)} //constructing the record with "MTD" as display value and sort key value to be any value less than 1 so that they cna be used later while sorting the display value column in matrix visual
            , {("YTD", INT(FORMAT(TODAY(), "YYYYMM")), 0)} //constructing the record with "MTD" as display value and sort key value to be any value less than the value assigned to "MTD" record so that they cna be used later while sorting the display value column in matrix visual
            )

The resulting custom calendar table of the above DAX looks as below in data view in Power BI:

The updated model will now looks as below:

Now place the “Display Value” column from the above “Custom Calendar” table in columns section and “Factory” column from “Production” table in rows section of the Matrix visual in Visual View. Initially, the matrix will look like the below with an error.

Ignore the error message for a moment and now define a measure with the name “Production Units New” to calculate the production units for the date value or “MTD”/”YTD” placed in the columns section and the “Factory” placed in the rows section of the matrix. Below is the DAX with comments defining the production units calculation.

Production Units New = VAR ReportingDate = TODAY()//read the current system date

VAR MTDBeginDate = IF(SELECTEDVALUE('Custom Calendar'[Display Value]) IN {"MTD"} //If the date dsiplayed in the header is "MTD" 
                        , EOMONTH(ReportingDate,-1)+1 //calculate first date of current month
                        , IF(SELECTEDVALUE('Custom Calendar'[Display Value]) IN {"YTD"} //If the date dsiplayed in the header is "YTD" 
                            , EOMONTH(ReportingDate, MONTH(ReportingDate)*(-1)) +1 //calculate first date of the year
                            , CONVERT(SELECTEDVALUE('Custom Calendar'[Display Value]), DATETIME) //else return the value displayed in header after converting into date
                            )
                    )

VAR MTDEndDate = IF(SELECTEDVALUE('Custom Calendar'[Display Value]) IN {"MTD", "YTD"} //If the date dsiplayed in the header is "MTD" or "YTD" 
                        , TODAY() //consider today as end date
                        , IF(CONVERT(SELECTEDVALUE('Custom Calendar'[Date Value]), DATETIME) > TODAY() //If the date dsiplayed in the header is neither "MTD" nor "YTD" and the value displayed in header after converting into date is greater than today's date then return blank
                            , BLANK()
                            , CONVERT(SELECTEDVALUE('Custom Calendar'[Date Value]), DATETIME) //otherwise return the value displayed in header after converting into date
                            )
                        )
//Begin date and end date calculated above will be same when the display value in the header is neither "MTD" nor "YTD".                         
RETURN CALCULATE(SUM([Production Units])
                , 'Calendar'[Date] >= MTDBeginDate
                , 'Calendar'[Date] <= MTDEndDate
                ) //calculate sum of production units for all the dates between start date and end date identified above

Now when you place this measure – “Production Units New” in the values section of this visual, the message disappears and calculates and displays production units for each of the dates as well as for MTD and YTD for each of the factory correctly as shown below. You can simply compare the excel report and this final output to check the correctness of the report.

Finally, an important point to keep in mind, any new measure that is to be placed in the customized matrix visual cannot directly rely on standard date dimension table (“Calendar” table in this example) and must be custom calculated using DAX following the same pattern as the “Production Units New” measure as defined above.

Here is the .pbix file to download and learn.

Hope that’s an interesting learning. Watch the space for more!