creating new measure if a field value = x

[davidmit]

Hi

I'm a newbie and this is first post. 

I have a table with a 'Year' field and a 'Value' field and a 'Currency' field.  I want to compare the year on year values for the value field for each currency. e.g.

2014 USD 100

2015 USD 110

I have tried to use Sum, SUMX, calculate, fileters etc to create new field '2015' with a value form the original value field provided that the year = 2015.  I was going to then do the same for 2014.  But I can't the new measure to work based upon the condition 'Year'=2015.

Any help greatly appreciated.

Thanks

Dave

[Baskar]

Pls shared some sample data and what the expected output. 

I will help u ... Quick dude

[davidmit]

Hi Baskar

3 fields of data

Year/Currency/Value

sample values

2015 USD 100

2016 USD 110

I want to create 2 new fields called for example 2015Value and 2016 Value, so

files now

Year/Currency/Value/2015Value/2016Value

Values

2015 USD 100 100 0

2016 USD 110 0     110

Many thanks

Dave

[Baskar]

This is tottaly Confusing dude , pls little bit clear or give only result that what u want from your data.

[davidmit]

Hi Baskar

I want to create 2 new measures.  One of them is '2015Value' and one of them '2016Value'. 

If the Year field = 2015 then I want to populate the '2015Value' with the original value.

If the Year field = 2016 then I want to populate the '2016Value' with the original value.

(If this had been excel, then in the 2015Value column I would have the formula =if(column A=2015,+value,"") and in the 2016Value column =if(column A =2016,+value,"")  assuming of course that the 'year' was in column A.)

So, if I have say 50 records, with a mixture of years and currencies, I can run a report that will show me;

Currency   2015Value    2016 Value

USD          100               110

Does this help?

Many thanks

Dave

[Baskar]

Use Matrix report that will help  to get your result.

[Baskar]

or 

If u want similar like excel in measures we can create  like

1. 2015value = CALCULATE(SUM('Currency'[Value]),FILTER('Currency','Currency'[Year]=2015))

2. 2016value = CALCULATE(SUM('Currency'[Value]),FILTER('Currency','Currency'[Year]=2016))

this will also help u dude

[davidmit · Accepted solution]

Hi Baskar

Yes, thanks.  I have used that syntax and my measure is as follows;

2015Sell = Calculate(Sum('Trading Data'[GBPSell]),filter('Trading Data','Trading Data'[TravelYear]=2015))

I now get the error message;

DAX compariosn operations do not support comparing values of type Text with values of type Integer.  Consider using the VALUE or FORMAT function to convert one of the values.  But that is a different issue.

Many thanks for your help.

Dave

[Baskar]

Change your Travelyear to text or add Quotes in 2015 . cool