Subtracting one column of a matrix from another and display result

[bematth]

I have a matrix with two date columns and need to display the difference between the two. So the result would look like:

  

So I start by creating two measures: 

And then created 

I'm not able to add this as a column to the existing matrix, so I've tried things like creating second matrix or table and hiding the row and column names so it looks like one visual 

But no matter what type of visual or how I arrange variables, I can't get the visual to generate.

Any suggestions?

[v-yalanwu-msft]

Hi, @bematth ;

Is your problem solved?  If so, kindly mark the proper reply as a solution to close the case. 

If not,

You could change it to:

nf2020=CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Year]=2020))

nf2021=CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Year]=2021))

Difference = [nf2020] - [nf2021]

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

[tamerj1]

Hi @v-yalanwu-msft 

this issue has been solved already. Thank you

[tamerj1 · Accepted solution]

@bematth 

I think the issue is here:

delete the quotations ( " " ) keep only the number

 

[tamerj1]

@bematth 

The above reply ☝️

[bematth]

Aha! That did it!

I swear, 9/10 my error is usally the result of something like that!

Thank you!

[tamerj1]

Hi @bematth 
Difference = [nf2020] - [nf2021]

It is also worth mentioning that it is a good practice to have a date table filtering your fact table.

[bematth]

I get the same results when using Difference = [nf2020] - [nf2021].

Based on the data source, I'm not sure a date table would be a possibility. 

[tamerj1]

@bematth 

Usually you can create a date table locally and connect it to your fact table. However, what do you mean by same result? You receive an error? What does the error say? Are you sure nf2020 and nf2021 are measure?

[bematth]

I get the same "Cannot display visual... comparison operators do not support comparing values of type integer with vlalues of type text..." error.

nf2020 and nf2021 are measures with the data type being whole number, as is nfdiff.  (And using VALUE didn't work the first time)

[tamerj1]

@bematth 

Would you please return the two measure alone without the difference and share the results.

[bematth]

As in just pull them into the visual instead? I get the same error:

[tamerj1]

@bematth 

Please see my last reply with the proposed solution. 

[bematth]

I thought I just did? I posted a screen shot as well. 

[OzkanDhont]

Hi @bematth !

I believe the problem resides in your year column.

You try to filter a text value "2020" with a numeric column.

Either change the type of year column to text.

Or change your filter to 2020 instead of  "2020".

Could you try it out and let us know if it worked?

Hope it helped!

Kind regards,

OD