Value of sales based on the latest date for each country

[Vibration85]

Hi everyone 🙂

I have a financial table with different fields like below:

Date	Inv Number	Country	Customer ID	Product ID	Qty	Sales
01 Feb 17	Inv01000	France	A001	DJAC02	37	1226.555
05 Feb 17	Inv01001	France	A003	DJAC03	28	928.2
02 Feb 17	Inv01011	France	G001	FSHO04	26	659.1
01 Feb 17	Inv01002	Germany	A006	DJUM01	40	1140
03 Feb 17	Inv01015	Germany	L003	FSHO04	37	937.95
04 Feb 17	Inv01016	Germany	M024	FBOO04	34	928.2
05 Feb 17	Inv01003	Germany	B004	DJUM02	32	912
01 Feb 17	Inv01004	Italy	B005	DJUM03	30	855
03 Feb 17	Inv01014	Italy	K001	FSHO04	27	684.45
02 Feb 17	Inv01005	Italy	C001	DDRE01	37	865.8
02 Feb 17	Inv01012	Canada	H002	FBOO04	23	627.9
01 Feb 17	Inv01013	Canada	J025	DJUM01	22	627
02 Feb 17	Inv01009	UK	E001	DDRE03	30	702
03 Feb 17	Inv01010	UK	F002	FSHO04	25	633.75
01 Feb 17	Inv01008	UK	D006	FSHO04	30	760.5

Now I gonna have something similar below that show me the value of the latest date for each country:

Date	Country	Latest Sale Value
02 Feb 17	Canada	627.9
05 Feb 17	France	928.2
05 Feb 17	Germany	912
03 Feb 17	Italy	684.45
03 Feb 17	UK	633.75

I created a new table from a list of countries and I gave the ID to each country as a master country table and tried to use this formula to create a measure but I have an error on it:

Latest Sales Value = CALCULATE(LOOKUPVALUE(financials[ Sales],financials[Date],MAX(financials[Date])))

could you please let me know what should I do?

many thanks 🙂

[harshnathani · Accepted solution]

Hi @Vibration85 ,

You can create a NEW TABLE

Table 4 =
SUMMARIZE (
    'Table',
    'Table'[Country],
    "Latest Date", CALCULATE (
        MAX ( 'Table'[Date] )
    ),
    "Sales Value", CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date]
                = MAX ( 'Table'[Date] )
                && 'Table'[Country]
                    = MAX ( 'Table'[Country] )
        )
    )
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Vibration85]

Many thanks, @harshnathani  it is working very well