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] )
)

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi cyberblitz ,

 

What is the logic of the expected output.

 

8:46 is for 3 columns related to Hotel 1.

 

Can you share the logic.

 

Regards,

Harsh Nathani

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

Anonymous 

The logic would be to have the MAX value from those from the same time

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi cyberblitz ,

     

     

    Create a measure

     

    MAxim = MAXX(SUMMARIZE('Table','Table'[ModifiedAt],'Table'[Rooms]),'Table'[Rooms])
     
     
     
     
    Regards,
    Harsh Nathani
     
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button
    • cyberblitz's avatar
      cyberblitz
      Icon for Helper II rankHelper II
      6 years ago

      I don't know why, but this is what I get.

      In SQL, this is how I would do it:

       

      select a.Facility, modif, rooms
from table a
inner join (
select Facility, max(modifiedAt) modif
from table
group by Facility
) b on a.Facility = b.Facility and a.ModifiedAt = b.modif