How to get value on latest date based on multiple columns?

[Anonymous]

Hello all,

I have dataframe like below:

Country	City	Neighbourhood	Date	Value	ExpectedValue
AX	BY	CT	1/14/2021	1	1
AX	BY	CT	1/13/2021	0	1
AX	BY	CT	1/12/2021	1	1
AX	BY	CT	1/11/2021	1	1
AX	BY	CT	1/10/2021	0	1
DX	EY	FT	1/11/2021	0	0
DX	EY	FT	1/10/2021	1	0
DX	EY	FT	1/9/2021	1	0
DX	EY	FT	1/8/2021	0	0

So for each Country-City-Neighbourhood pair I have multiple dates and each date has its own value(1 or 0)
I need to get the 'Value' on latest date as shown in 'ExpectedValue' column.

How can I write the DAX formula?

Any comment would be helpful.

Thanks a lot!

[Fowmy · Accepted solution]

@Anonymous 

Add this code as a new column to your table. Replace table and column names as necessary.

Exp Value = 
var __Date = 
MAXX(
    FILTER(
        'Table 7',
        'Table 7'[Country] = EARLIER('Table 7'[Country]) &&
        'Table 7'[City] = EARLIER('Table 7'[City]) &&
        'Table 7'[Neighbourhood] = EARLIER('Table 7'[Neighbourhood])      
    ),
    'Table 7'[Date]
)

var __Value = 
    MAXX(
    FILTER(
        'Table 7',
        'Table 7'[Country] = EARLIER('Table 7'[Country]) &&
        'Table 7'[City] = EARLIER('Table 7'[City]) &&
        'Table 7'[Neighbourhood] = EARLIER('Table 7'[Neighbourhood]) && 
        'Table 7'[Date] = __Date    
    ),
    'Table 7'[    Value]
)

return

__Value

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn