Need help - Column difference in a matrix

[Anonymous]

Good afternoon all!

I'm in dire need of some help.

I have the following matrix making use of 3 fields:

• Description
• Period
• Cost

My issue is the following, i want to add a column next to the 2 dates which does a value of 202002 - value of 202001 

i want it to only to the difference based on the 2 periods i selected in the filter visual.

Any help would be appreciated or guidance on how to do it better.

Regards 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures

Measure 2 =
VAR m1 =
    MINX (
        ALLSELECTED ( 'Table 2' ),
        [period]
    )
VAR m2 =
    MAXX (
        ALLSELECTED ( 'Table 2' ),
        [period]
    )
RETURN
    CALCULATE (
        SUM ( 'Table 2'[cost] ),
        FILTER (
            'Table 2',
            'Table 2'[period] = m2
        )
    )
        - CALCULATE (
            SUM ( 'Table 2'[cost] ),
            FILTER (
                'Table 2',
                'Table 2'[period] = m1
            )
        )


Measure = IF(ISINSCOPE('Table 2'[period]),SUM('Table 2'[cost]),[Measure 2])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

thank you very much! Works like a charm!

thanks to everyone else who also gave inputs and solutions.

appreciate it!

[Nishantjain]

@Anonymous 

Below is a suggestion

Create 3 measures

Measure 1

Previous Period = 
VAR mindate = min(period)
return
Calculate(Sum(Data[values]), Period[period] = mindate)

Measure 2: 

Latest period= 
VAR maxdate = max(period)
return
Calculate(Sum(Data[values]), Period[period] = maxdate)

Measure 3: 

Variance = [Measure 1] - [Measure 2]

Drop each of these measures in your matrix and remove the period field from the matrix

Few things to note:

1. If you select more than 3 period, it will automatically default to 2 period selecting the minimum and maximum date

2. The column headers will not show the period name, but would show the name of the measure

[amitchandak]

Make sure period are in a separate table for better working

Measure =
var _min = minx(Period,Period[Name])
var _max = maxx(Period,Period[Name])
return
calculate(sum(table[cost]),filter(all(Period),Period[Name]=_min)) -calculate(sum(table[cost]),filter(all(Period),Period[Name]=_max))

if you need more help make me @

Appreciate your Kudos.

[Anonymous]

thanks @amitchandak , i tried this and didnt give any errors, but it doesnt want to do the calculation. It just returns a 0.

[amitchandak]

Can you share sample data and sample output.