Create a table/matrix where rows and colums are the same field and the values are the difference

[Tasnalem]

Hi,

I am trying to show the difference of the VALUE column month by month. I explain:

This is my dataset. I have two columns, one is the column "value_date" in format dd/mm/yyyy and the other one is the column "value" with the decimal value.

The business goal is create this table/matriz in Power BI to compare VALUE column between months, for example, if the sum of January is = 1000 and sum of February is 800, the value in the table will be 200 ( or -200).

SUM( value, value_date =  "JANUARY") - SUM( value, value_date =  "FEBRUARY")

As you can see, the diagonal will be always 0, because you are always compare the same month with itself

	January	February	March	[....]	November	December
January	0	20,5 ( January - February )	7,6 ( January - March)	5,4	10	12,3
February	-20,5	0	8	7,2	2,7	5,9
March	-7,6	8	0	4,9	1,2	0,9
[....]	-5,4	-7,2	-4,9	0	7,6	5,9
November	-10	-2,7	-1,2	-7,6	0	1
December	-12,3	-5,9	-0,9	-5,9	-1	0

Any idea?

Thansk in advance

[ERD]

@Tasnalem , not sure this is what you need, but you can try this approach:

1. Create a second Date table.

2. Use both in Matrix visual.

3. Create a measure:

test = 
var m_1 = SELECTEDVALUE('Date'[YYYYMM])
var m_2 = SELECTEDVALUE(Date_2[YYYYMM])
var x1 = CALCULATE(SUM(Data[Value]), REMOVEFILTERS('Date_2') )
var x2 = CALCULATE(SUM(Data[Value]), REMOVEFILTERS('Date') )
return 
x1-x2

Data:

Result:

[Tasnalem]

Hello,

Thank you for your help. but with this solution I have a table only with a diagonal with "0". I don't have the difference between differents months:

[v-zhengdxu-msft]

Hi @Tasnalem 

I have understood your problem.

You need to create a matrix with rows and columns that are months, and the values in the table are the difference between the values of the month.

Here is the solution:

First, I created a set of reference data.

Then, extract the month.

With the DAX:

------------------------------------------------------------

Month = FORMAT([value_date],"mmmm")

------------------------------------------------------------

Then, a table 2 is created based on the value and month of Table 1

------------------------------------------------------------

Table 2 = SELECTCOLUMNS('Table',[Month],[value])

------------------------------------------------------------

Next, use the crossjoin function to create a new table.

The table contains the Cartesian product of all rows from all tables in the arguments

------------------------------------------------------------

Table 3 = CROSSJOIN('Table','Table 2')

------------------------------------------------------------

Create a measure to calculate the difference for the corresponding month:

------------------------------------------------------------

Measure = SUM('Table 3'[value])-SUM('Table 3'[Table_value])

------------------------------------------------------------

Then create a Matrix, Row is Month, Columns is Table_Month, Values is Measure:

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tasnalem]

Hello,

Thanks for the answer, that is want i want to achieve, but this solution is not suitable for huge dataset, because I have an error related to memory failure when I do the CROSSJOIN step. Do you know any other solution without this CROSSJOIN?

[Ashish_Mathur]

Hi,

Not clear with the business context of your requirement.  Why are you dragging Month/Year to the rows and columns?  What business question is getting answered by the differences which you want to see in the body of the matrix?

[Tasnalem]

Hello Ashish,

Thanks for your answers. I edited the post with new information to clarify and answer your doubts

[amitchandak]

@Tasnalem , Create a date table and join the Date with that

You can create month on month measure like

example measures

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

For Cohort Analysis, Customer Retention %- https://youtu.be/qY1SDF1cwsg