Difference between 2 columns in a Matrix Table

[HEW]

Hi.

I am trying to calculate the difference between 2 columns i Desktop.

The value is a measure and the  columns are one field, pls. see below.

I have tried to create a measure but with no luck. 

For January the new column should be -15

February -12

......

July +38

Any suggestions?

Thanks a lot.

[Ashish_Mathur]

Hi,

If there is also a Date column available in your dataset, then you should create a relatiosnhip between this Date column and the Date column of your Calendar Table.  In the Calendar Table, write the following calculated column formulas to extract the Year and Month

Year=year(Calendar[Date])

Month=FORMAT(Calendar[Date],"mmmm")

In your visual, drag Year and Month from the Calendar Table.  Then write this measure

[Proposals Count]-CALCULATE([Proposals Count],SAMEPERIODLASTYEAR(Calendar[Date]))

Hope this helps.

[HEW]

Hi Ashish.

It works perfectly! Would it be possible to leave out the difference for 2017 as it is the same value as the count?

Br. Hele

[Ashish_Mathur]

Hi,

I don't think that is possible.

[v-frfei-msft]

Hi @HEW,

Please refer to this case to find the solution.

https://community.powerbi.com/t5/Desktop/Difference-between-2-columns-in-a-Matrix-Table/td-p/81614/p...

Regards,

Frank

[HEW]

Hi Frank.

Is it really so complicated? I'll try and adapt the formula to my data and hope it works  Thanks.

Br. Helen

[v-frfei-msft]

Hi @HEW,

I made one sample for your reference.

1. Enter the data as your description and create a calculated table.

Table = SUMMARIZE(Table1,Table1[Month],Table1[mo],Table1[Year],"sum",SUM(Table1[Amount]))

2. Create a calculated column in Table.

diff = var prevalue = CALCULATE(FIRSTNONBLANK('Table'[sum],1),
FILTER('Table',('Table'[Year]= (EARLIER('Table'[Year])-1)) && 'Table'[mo]= EARLIER('Table'[mo])))
return
IF(ISBLANK(prevalue), BLANK(),'Table'[sum]-prevalue)

3. Then we can get the result as we excepted.

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

Hi @HEW,

Why cant you show the Year on Year measure next to 2018 column in the table?

Regards

Lokesh

[HEW]

I'm not sure I know what you mean.... pls. elaborate 

[Anonymous]

Hi @HEW,

i mean to say that why cant you use the Year over Year Change measure in the table next to 2018?

[HEW]

I have tried to add a quick measure as you suggest but it only returns 0,-. But thanks!

Br. Helen