How to calculate a difference between 2 column.

[Shravan16]

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 the different books.
Also what is the best dashboard visually to comapre the changes between 2 end of month? 
Please find below data :  

Thank you. 

[Anonymous]

Hi @lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution.
@Shravan16 , if this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it.

Then I add a measure:

MEASURE =
VAR _currentbook =
    SELECTEDVALUE ( 'Table'[Book] )
VAR _previousMonth =
    MINX ( ALL ( 'Table' ), MONTH ( 'Table'[Month] ) )
VAR _currentMonth =
    MONTH ( MAX ( 'Table'[Month] ) )
RETURN
    IF (
        _currentMonth <> _previousMonth,
        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]

Thanks @Anonymous.
I have one additional question - what if the Month book A has sub-catergory - for eg Sub Category 1
                                                                                                                                           Sub Category 2
How to include the calculation of the difference for both the Book A and the sub-category. 
For eg the Book A difference is -127. When I drill down the Book A on the MAtrix , I have Sub Category 1 and 2. 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. 

Thank you for you help.  

[Anonymous]

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]

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 · Accepted solution]

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.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...