The Power BI Formatting Matrix includes changing the Matrix Grid Colors, Row formatting, Column formatting, row and column colors, Matrix Title text, and background color, etc. I want it in this way to be sorted Dec 2016(Dec 2015),Jan 2017(Jan 2016) and Feb 2017(Feb 2016) in the chronological order.How to Format Power BI Matrix report with an example?. But here the sorting happens in this way.Dec 2015, Jan 2016,Feb 2016,Dec 2016,Jan 2017 and Feb 2017. then i sorted it according to the Year&month column. Then i wrote a dax and created custom column to sort it according to Year&month. when i sort the x-axis according to month no, then the values on x-axis is displayed in this order- ” Jan 2017,Feb 2017 and Dec 2016″(since month no of the specified months are 1,2 and 12 respectively). But the problem am facing here is sorting the x-axis. then the chart should show for Dec 2016(Dec 2015),Jan 2017(Jan 2016) and Feb 2017(Feb 2016) in the chronological order but its not happening.The months mentioned in the brackets are for same period last year. Suppose I choose February 2017 and the “n value” is -3. However, I wanted to show ‘same period last year(month) and current month’ comparison and am using Clustered column chart to display current month and same period last year bars. Hi, I really loved this and appreciate it. Owen Auger ( twitter) has come up with an easier formula, use this one instead of mine –ĭATESINPERIOD ( ‘Date', MAX ( ‘Date' ), – , MONTH )ĥ) Now create a bar chart with MonthYear on Axis and Sales (last n months) on the values, as shown below.Īt this point, you can change the month in the filter, as well as the value of N and see the bar chart change (as well as the other measures). Note that we are ignoring the date filter, only respect the date in Fact ) - if the date in the fact table is between the last N months, display Sales, else nothing. MonthYearNo = RELATED ( ‘Date' )ĬALCULATE ( MAX ( Sales ), ALL ( ‘Date’ ) ) - ignore the selected date filter, and find the max of date in Sales tableĮNDOFMONTH ( ‘Date' ) - get the last day of the month selected in the date filterĮDATE ( FDate, – ) - get the last day of -N monthsĬALCULATE ( SUM ( Sales ), ALL ( ‘Date’ ) )

Create the 2 calculated columns in the Sales table (MonthYear and also MonthYearNo for sorting the MonthYear column), as well as a measure – Sales (last n months). It is important to know that putting the Month from the Date table will not work, so what we are going to do is create a month column in the Sales table and then use that as the axis for the bar chart. Place it in the chart as shown belowĤ) The main step for this technique is – create a measure that will display the sum of sales for the last N months. Let us create a What If parameter called N with values from 1 to 24, and increments of 1. Sales YTD = TOTALYTD ( SUM ( Sales ), ‘Date' )ģ) The next step is to make a measure that will display the last N months. Sales Last Year = CALCULATE ( SUM ( Sales ), SAMEPERIODLASTYEAR ( ‘Date' ) ) You can change the month in the slicer and verify that the measure values change for the selected month.

Below, you can see that I have displayed 3 regular measures based on the selected month (Sales for selected month, Sales YTD and Sales Last Year) along with a bar chart that shows the sales for the last N months based on the selected month (special thanks to the folks at PowerBI.tips for the pretty layout).įollow the steps below to recreate the same:-ġ) For the purpose of this post, I am using a very simple model – a Sales table (with just Date and Sales) and a Date table.Ģ) Create 3 measures as shown below, and then add those 3 measures in the report along with a month slicer as shown below. Most of the techniques for doing the same use a disconnected date dimension along with the regular date dimension however, using the same date dimension as a filter has the additional benefit that you can use all of the other measures also without any changes in the same report.īefore I show you the technique, let me show you an example of a finished report. It is Friday, and I thought of writing a quick tip on how you can use the same date dimension for displaying the last N months (say, in a bar chart) and the data for selected month (say, in a card visual).

Power bi matrix tabular format