Forum Discussion

Shravan16's avatar
Shravan16
New Member
2 years ago
Solved

How to calculate a difference between 2 column.

Hello. I am trying to calculate the difference between 2 columns for the month from the matrix option on power BI but am unable to do it correctly. For example - Difference between Feb and Jan for t...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Shravan16 

     

    Please try this:

    As what you want, I create a new sample:

    The measure could be like:

    Measure 3 = 
	VAR _currentbook = SELECTEDVALUE('Table'[Book])
	VAR _currentSub = SELECTEDVALUE('Table'[Sub])
	VAR _previousMonth = MINX(
		ALL('Table'),
		MONTH('Table'[Month])
	)
	VAR _currentMonth = MONTH(MAX('Table'[Month]))
	RETURN
		IF(
			_currentMonth <> _previousMonth,
			IF(
				HASONEVALUE('Table'[Sub]),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Sub] = _currentSub && 'Table'[Book]=_currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Sub] = _currentSub && 'Table'[Book]=_currentbook
					),
					'Table'[Open Nominal]
				),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				)
			)
		)

    The result is as follow:

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Shravan16 

 

Please try this:
I update this sample:

Measure 3 = 
	VAR _currentbook = SELECTEDVALUE('Table'[Book])
	VAR _currentSub = SELECTEDVALUE('Table'[Sub])
	VAR _previousMonth = MINX(
		ALL('Table'),
		MONTH('Table'[Month])
	)
	VAR _currentMonth = MONTH(MAX('Table'[Month]))
	RETURN
		IF(
			_currentMonth <> _previousMonth,
			IF(
				HASONEVALUE('Table'[Sub]),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Sub] = _currentSub
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Sub] = _currentSub
					),
					'Table'[Open Nominal]
				),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				)
			)
		)

 The result is as follow:

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.

Shravan16's avatar
Shravan16
New Member
2 years ago

Hi  Anonymous, 

It works now excepts when i have the same category in different books then the DAX does not work. 
For example the Book A difference is -127. When I drill down the Book A on the MAtrix , I have Sub Category A1 and A2. The expected outcome is having the difference of -127 comes from Sub-Category 1 Diff which is -120 and Sub category 2 Diff which is  -7 which leads to the total -127 for the Book A.  Book B can consist of Sub Category A1 (-140) and B1 (-5) which equals to total difference of -145 for Book B. The sub Category A1 is the same product but can be in different books with different Amount. 

Thank you.  


  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Shravan16 

     

    Please try this:

    As what you want, I create a new sample:

    The measure could be like:

    Measure 3 = 
	VAR _currentbook = SELECTEDVALUE('Table'[Book])
	VAR _currentSub = SELECTEDVALUE('Table'[Sub])
	VAR _previousMonth = MINX(
		ALL('Table'),
		MONTH('Table'[Month])
	)
	VAR _currentMonth = MONTH(MAX('Table'[Month]))
	RETURN
		IF(
			_currentMonth <> _previousMonth,
			IF(
				HASONEVALUE('Table'[Sub]),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Sub] = _currentSub && 'Table'[Book]=_currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Sub] = _currentSub && 'Table'[Book]=_currentbook
					),
					'Table'[Open Nominal]
				),
				SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _currentMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				) - SUMX(
					FILTER(
						ALLSELECTED('Table'),
						MONTH('Table'[Month]) = _previousMonth && 'Table'[Book] = _currentbook
					),
					'Table'[Open Nominal]
				)
			)
		)

    The result is as follow:

    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.