Forum Discussion
Calculate the difference between multiple columns in matrix
Hello!
I'm scratching my brain out of this calculation.. Can anyone maybe help me on the way if it's even possible? :)
I'm trying to calculate difference between multiple columns in a Matrix.
I just want to see the different between each "Date" column of my choice, for example to see how many calls where made in "2018-03" vs "2019-03".
So the "Total Difference" is the correct value but since i want to select more than just the two dates then i can't use the "Total" value anymore because i just want to see the difference to the "Count of Call ID" value of the right column.
The "Difference" measure is:
Difference = CALCULATE (
COUNT('Table1'[Call ID]),
FILTER ('Table1', 'Table1'[Date] = MAX ('Table1'[Date]))
)
- CALCULATE (
COUNT('Table1'[Call ID]),
FILTER ('Table1', 'Table1'[Date] = MIN ('Table1'[Date]))
)Example data:
Anonymous ,
Create the following column in your table.NEWDATE = DATE(LEFT(Table1[Date],4),RIGHT(Table1[Date],2),1)
Then create a new table using DAX below.Table = SUMMARIZE(Table1,Table1[Cities],Table1[Date],Table1[NEWDATE],"countvalue",COUNT(Table1[Call ID]))
Create the diff column in the new table as below.
diff = var pre= CALCULATE(FIRSTNONBLANK('Table'[countvalue],1),FILTER('Table','Table'[NEWDATE]>EARLIER('Table'[NEWDATE])&&'Table'[Cities]=EARLIER('Table'[Cities]))) return IF(ISBLANK(pre),BLANK(),pre-'Table'[countvalue])
Regards,
Lydia
1 Reply
Anonymous ,
Create the following column in your table.NEWDATE = DATE(LEFT(Table1[Date],4),RIGHT(Table1[Date],2),1)
Then create a new table using DAX below.Table = SUMMARIZE(Table1,Table1[Cities],Table1[Date],Table1[NEWDATE],"countvalue",COUNT(Table1[Call ID]))
Create the diff column in the new table as below.
diff = var pre= CALCULATE(FIRSTNONBLANK('Table'[countvalue],1),FILTER('Table','Table'[NEWDATE]>EARLIER('Table'[NEWDATE])&&'Table'[Cities]=EARLIER('Table'[Cities]))) return IF(ISBLANK(pre),BLANK(),pre-'Table'[countvalue])
Regards,
Lydia