Caluclated column for variance b/w periods

[frmtaat]

Dear All,

I would like to add a new coulmn to a matrix to see the delta between the periods. Let's say i have the following raw data:

Period	GL	Amount
1	100	50
1	200	100
2	100	60
2	200	200

I have created the following matrix:

Column: Period; Raw: GL; Value: Amount. Now it looks like this:

	1	2	DELTA
100*	50	60	=60-50
200*	100	200	=200-100

So i would like to add coulmn "DELTA" to the matrix, but i am sturggeling to refere to period's "underlying value" if you know what i mean...

Could you help please? Thanks!

[v-piga-msft · Accepted solution]

Hi @frmtaat ,

Actually, we cannot get your desired martix visual in power bi directly.

However, there is a workaround that we could do that. Please follow the steps below.

1. You could make a little change for the formula provided by Mariusz .

Diff = 
VAR tbl = ALLEXCEPT(YourTable, YourTable[GL])
VAR currentPeriod = YourTable[Period]
VAR previousPeriod = CALCULATE(
    MAX(YourTable[Period]),
    tbl,
    YourTable[Period] < CurrentPeriod
) 
VAR previousAmount = CALCULATE(
    MAX(YourTable[Amount]),
    tbl,
    YourTable[Period] = previousPeriod
)
RETURN
IF('YourTable'[Period]=1,BLANK(),YourTable[Amount] - previousAmount)

2. Create the matrix :

Column: Period; Raw: GL; Value: Amount and Diff. Now it looks like this:

3. You could use mouse to hide the column Diff ,Total Amount and turn off the Word warp under formatting, then your will get your desired output like below.

In addition, you could refer to my attachment.

Best  Regards,

Cherry

[frmtaat]

First of all thanks so much for both of your answers and sorry to come back so late, i have a lot going on right now and have little time to deal with PBI...

What i still don't get is when we declear the variable "previousAmount" and assing the value with the CALCULATE function why we need the MAX function also? As we are trying to grab the amount here, we do not need MAX right?

Thanks,

Mate

[Mariusz]

Hi @frmtaat 

You can add a column like below to your table.

Column = 
VAR tbl = ALLEXCEPT(YourTable, YourTable[GL])
VAR currentPeriod = YourTable[Period]
VAR previousPeriod = CALCULATE(
    MAX(YourTable[Period]),
    tbl,
    YourTable[Period] < CurrentPeriod
) 
VAR previousAmount = CALCULATE(
    MAX(YourTable[Amount]),
    tbl,
    YourTable[Period] = previousPeriod
)
RETURN
YourTable[Amount] - previousAmount

It wold be good idea to add a year to your Period column like 201901, so when it restarts from 1 the 12 can still be picked as previous 

Regards,
Mariusz

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