Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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?

Any comment would be helpful.

Thanks a lot!

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
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

 

Fowmy_0-1610628774109.png

________________________

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

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

1 REPLY 1
Fowmy
Super User
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

 

Fowmy_0-1610628774109.png

________________________

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

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.