cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

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

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?

Thanks a lot!

1 ACCEPTED SOLUTION
Super User

@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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

@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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors