Calculated measure to show the difference between two date's values in a matrix.

[kishanr]

I have a Power BI matrix which contains platform name as the rows, month as the column which I have filtered to show two months, and finally the values are the AUM value. I wanted to create a column in my matrix which works out the difference between the two month dated columns AUM values. The two month date columns are from the same field, months, in my main data table. I have just filtered on two specific months so they show side by side in my matrix.

[v-zhengdxu-msft]

Hi @kishanr 

I have a certain understanding of the question you asked:

You want to calculate the difference between the values of AUM for the two months filtered out by the matrix and display it in the matrix.

The solution is as follows:

First, I created a set of data with three columns: AUM, Month, platform.

Then create a matrix with the column being Month, the row being platform, and the value being AUM.

After this, a measure is created:

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

DIFFERENCE =

VAR DATE_current = MIN(Sheet1[Month])

VAR DATE_previous = MAXX(FILTER(ALLSELECTED(Sheet1),[Month]<DATE_current),[Month])

return CALCULATE(SUM(Sheet1[AUM]),'Sheet1'[Month]=DATE_current)-CALCULATE(SUM(Sheet1[AUM]),'Sheet1'[Month]=DATE_previous)

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

DATE_current is used to extract the current value of the month on the represent.

DATE_previous is the previous value of the month used to select the current value.

What is returned is the AUM of the current month minus the AUM of the previous month.

Drag DIFFERENCE into the Value box, the result is shown below:

Using this method, you can select multiple months at the same time for comparison, and the DIFFERENCE column below each month is the difference between the AUM of the current month and the AUM of the previous month.

[kishanr]

Hi,

Thanks for your response. Apologies if I was not clear but I had a couple points to add:

My main table has the format below in power query:

My matrix in report view has this format:

I also imported another table in power query which contains dates which has this format:

I have created a relationship in model view between my main table and the dates tables so that I can represent the dates in different formats on my power bi visuals.

This is the format of the matrix I want to add the AUM difference to as a measure:

I tried using your calculation but it creates a difference for both dates selected on my matrix. I referred to the 'Date' field in my main table instead of Months as the Months is from another table. Please see my calculation below which I used.

AUM Difference =

VAR DATE_current = MIN('Combined Table'[Date])

VAR DATE_previous = MAXX(FILTER(ALLSELECTED('Combined Table'),[Date]<DATE_current),[Date])

return CALCULATE(SUM('Combined Table'[AUM]),'Combined Table'[Date]=DATE_current)-CALCULATE(SUM('Combined Table'[AUM]),'Combined Table'[Date]=DATE_previous)

I only want the new measure, 'AUM Difference', to be dynamic and calculate the difference between two months that are selected from the slicer.

Thanks in advance if you can help.

[v-zhengdxu-msft · Accepted solution]

Hi @kishanr 

I've made some changes based on your needs,

I've created a set of sample data:

Then add a calculated column:

--------------------------------------------------------
MONTH = FORMAT('Sheet1'[Date],"mmm yyyy")
--------------------------------------------------------

Then add a measure:

--------------------------------------------------------------
DIFF =

VAR PT = SELECTEDVALUE(Sheet1[Platform])

// Extract the current value

VAR Date_current = MIN('Sheet1'[Date])

// Extract the current date

VAR Date_previous = MAXX(

        FILTER(

            ALLSELECTED('Sheet1'),

            'Sheet1'[Date] < Date_current && MONTH('Sheet1'[Date]) <> MONTH(Date_current)

        ),

        'Sheet1'[Date]

    )
// Extract the previous date

    RETURN

        IF(

            Date_previous <> BLANK(),

            CALCULATE(

                SUM('Sheet1'[AUM]),

                FILTER(

                    ALLSELECTED('Sheet1'),

                    MONTH('Sheet1'[Date]) = MONTH(Date_current) && YEAR('Sheet1'[Date]) = YEAR(Date_current)&&'Sheet1'[Platform]=PT

                )

            ) - CALCULATE(

                SUM('Sheet1'[AUM]),

                FILTER(

                    ALLSELECTED('Sheet1'),

                    MONTH('Sheet1'[Date]) = MONTH(Date_previous) && YEAR('Sheet1'[Date]) = YEAR(Date_previous)&&'Sheet1'[Platform]=PT

                )

            )

        )

// Returns the difference between two months

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

Use [Month] to create a slicer;

Create a Matrix:

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.}

[123abc]

Option 1: Using a Calculated Column (Static)

If you want to add a calculated column to your table, you'll have a static value for the difference between two specific months. This method is not dynamic; it's suitable if you always want to compare two specific months.

1. Create a new table (or you can add a calculated column to your existing table) that represents the difference between the two months.

Difference Between Months =
VAR AUM_Value_Month1 = CALCULATE(SUM('YourTableName'[AUM]), FILTER('YourTableName', 'YourTableName'[Month] = "Month1"))
VAR AUM_Value_Month2 = CALCULATE(SUM('YourTableName'[AUM]), FILTER('YourTableName', 'YourTableName'[Month] = "Month2"))

RETURN
AUM_Value_Month1 - AUM_Value_Month2

Option 2: Using a Calculated Measure (Dynamic)

Creating a measure would make the comparison dynamic, meaning you can select any two months, and the measure will provide the difference between them.

1. Create a new measure in your table.

Difference Between Months =
CALCULATE(
SUM('YourTableName'[AUM]),
FILTER(
ALL('YourTableName'[Month]),
'YourTableName'[Month] = "Month1"
)
) -
CALCULATE(
SUM('YourTableName'[AUM]),
FILTER(
ALL('YourTableName'[Month]),
'YourTableName'[Month] = "Month2"
)
)

Replace YourTableName with the actual name of your table. Also, replace "Month1" and "Month2" with the actual month names or identifiers you have.

After creating the measure, drag it into your matrix visual. When you have both months side by side in your matrix, this measure will dynamically calculate the difference between the AUM values for those two months.

This approach allows you to get a dynamic difference in the matrix, adjusting as you filter or change the months you're comparing.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.