Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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!

 

 

Need Help
Show and Tell
Tips and Tricks
  • Fowmy's avatar
    Fowmy
    5 years ago

    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

     

1 Reply

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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