Calculate the Diffrence of two filtered row in Matrix

[amontemayor]

Good Day!

Can anyone suggest how to calculate the difference of the Quantity of the two given months? differen

[amontemayor]

[v-yanjiang-msft · Accepted solution]

Hi @amontemayor ,

According to your description, here's my solution.

1.Create a month number column, because the month column in text compared by alphabetical instead of real month.

If you have a date column in the table, simply create a calculated column.

MonthNum = MONTH([Date])

If not, a calculated column like this instead:

MonthNum=SWITCH([MonthName],
"January",1,
"February",2,
"March",3,
"April",4
.......

2.Create a measure.

Diff =
VAR _Min =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Item Description] = MAX ( 'Table'[Item Description] )
                && 'Table'[MonthNum] = MINX ( ALLSELECTED ( 'Table' ), 'Table'[MonthNum] )
        ),
        'Table'[Quantity]
    )
VAR _Max =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Item Description] = MAX ( 'Table'[Item Description] )
                && 'Table'[MonthNum] = MAXX ( ALLSELECTED ( 'Table' ), 'Table'[MonthNum] )
        ),
        'Table'[Quantity]
    )
RETURN
    _Max - _Min

Then get the difference of selected two month. The result is quantity of big month minus quantity of small month. You can either put it in the matrix or a seperate table visual with the Item Description column.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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