Forum Discussion

cyberblitz's avatar
cyberblitz
Icon for Helper II rankHelper II
6 years ago
Solved

Find last value by date

I am struggling to solve this. I have searched and tried numerous formulas to no avail. The dataset is thus(sorry about the formatting): District Facility Shift                 Rooms ModifiedAt ...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi cyberblitz ,

     

    Based on your sample, I got the following result. Is it your expected output?

     

    Here are two measures I used.

    Max Date =
CALCULATE (
    MAX ( 'Table'[ModifiedAt] ),
    ALLEXCEPT ( 'Table', 'Table'[Facility] )
)
    Value =
CALCULATE (
    MAX ( 'Table'[Beds] ),
    FILTER ( 'Table', 'Table'[ModifiedAt] = [Max Date] )
)

     

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi cyberblitz ,

 

Based on your sample, I got the following result. Is it your expected output?

 

Here are two measures I used.

Max Date =
CALCULATE (
    MAX ( 'Table'[ModifiedAt] ),
    ALLEXCEPT ( 'Table', 'Table'[Facility] )
)
Value =
CALCULATE (
    MAX ( 'Table'[Beds] ),
    FILTER ( 'Table', 'Table'[ModifiedAt] = [Max Date] )
)

 

cyberblitz's avatar
cyberblitz
Icon for Helper II rankHelper II
6 years ago

v-eachen-msft 

 

Yep, that's it. Many Thanks. I suppose it's similiar to the SQL script i created to produce the same result, where it refers upon itself..