How create a condition with LOOKUPVALUE

[Anonymous]

Hi! 

I have two different datas, as in the tables below

country	date	type	value
peru	01/01/2023	old	5000
peru	02/01/2022	old	4000
peru	03/01/2022	old	8000
peru	04/01/2022	old	3000
peru	01/01/2023	new	1000
peru	02/01/2022	new	8000
peru	03/01/2022	new	500
peru	04/01/2022	new	9000
USA	01/01/2023	old	4000
USA	02/01/2022	old	6000
USA	03/01/2022	old	7000
USA	04/01/2022	old	2000
USA	01/01/2023	new	5000
USA	02/01/2022	new	8000
USA	03/01/2022	new	9000
USA	04/01/2022	new	4000

country	date	value A
peru	01/01/2023	4444
peru	02/01/2022	8000
peru	03/01/2022	9000
peru	04/01/2022	8000
USA	01/01/2023	5555
USA	02/01/2022	12000
USA	03/01/2022	6000
USA	04/01/2022	889

I tried add the column Value New using the code below, but it does not work

Value new = IF('data'[type] = "new", LOOKUPVALUE (

    'data'[value],

    'data'[date], [date]),
    'data'[country], [country], "0")

the end result would be this

country	date	value A	Value Old	Value new
peru	01/01/2023	4444	5000	1000
peru	02/01/2022	8000	4000	8000
peru	03/01/2022	9000	8000	500
peru	04/01/2022	8000	3000	9000
USA	01/01/2023	5555	4000	5000
USA	02/01/2022	12000	6000	8000
USA	03/01/2022	6000	7000	9000
USA	04/01/2022	889	2000	4000

[Dangar332]

hi, @Anonymous 
i am done with different method
your first table as a1 and ypur second table as a2 and and create relationship  bw date column

     

create measure

value old =

CALCULATE(SUM(a1[value]),a1[type]="old",KEEPFILTERS(a1[country]=SELECTEDVALUE(a2[country])))

 

create another measure

value new =

CALCULATE(SUM(a1[value]),a1[type]="new",KEEPFILTERS(a1[country]=SELECTEDVALUE(a2[country])))

Did i answer your question? Mark my post as a solution which help other people to find  fast and easily.

[Anonymous]

Are the other way that you don't need to create a relationship?

For me, this way it doesn't work

[Dangar332]

hi, @Anonymous 

try below solution without using of relationship bw tables
Measure (1).

valueold =

CALCULATE(SUM(a1[value]),

           a1[type]="old",

           KEEPFILTERS(a1[country2]=SELECTEDVALUE(a2[country1])&&

                        a1[date]=SELECTEDVALUE(a2[date1])

                      )

         )

 Measure (2).

valuenew =

CALCULATE(SUM(a1[value]),

             a1[type]="new",

             KEEPFILTERS(a1[country2]=SELECTEDVALUE(a2[country1])&&

                           a1[date]=SELECTEDVALUE(a2[date1])

                        )

        )

which give you below outout 

here i am not use relationship bw table

i am provide link of  .pbix  file. refer Here 

Did i answer your question? Mark my post as a solution which help other people to find  fast and easily.

[SQL_SousChef]

is the goal to have both a old value and new value? I am not sure I understand the question here but if you want the "new" value for each country/date then you can use this: 

NewValue =

SUMX ( FILTER ( data, data[type] = "new" ), data[value] )

[Anonymous]

I tried this solution, but returns only the general sum of value for just type . I want the value sum by data, country and type. I would like to use LOOKUPVALUE  (using arguments data and country) with filter by type

I tried this too, but it doesn't work

New = LOOKUPVALUE (

    'a1'[value],

    'a1'[date], a2[date],

    'a1'[country], a2[country],

    'a1'[type], "new")

[SQL_SousChef]

Can you try putting the country and date in a table then add the measure I provided. It should show you the new value for each date and country combo.