Find last value by date

[cyberblitz]

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

hotels   hotel1   Night 9:00 pm    22      12/06/2020 08:46:50
hotels   hotel1   AM 7:00 am        24     12/06/2020 08:46:40
hotels   hotel1   AM 7:00 am        0       12/06/2020 08:46:30
hotels   hotel2   PM 3:00 pm        5      12/06/2020 07:38:50
hotels   hotel2   Night 9:00 pm    4      12/06/2020 07:38:40
hotels   hotel1   Night 9:00 pm    23    11/06/2020 17:29:50
hotels   hotel1   PM 3:00 pm        22    11/06/2020 17:12:40
hotels   hotel1   Night 9:00 pm    23    11/06/2020 17:12:30
hotels   hotel2   PM 3:00 pm        5     11/06/2020 15:27:50
hotels   hotel2   Night 9:00 pm    4     11/06/2020 15:26:40
hotels   hotel2   Night 9:00 pm    5     11/06/2020 15:26:30
hotels   hotel2   PM 3:00 pm        4     11/06/2020 15:26:50
hotels   hotel2   Night 9:00 pm    5     11/06/2020 15:26:40
hotels   hotel2   Night 9:00 pm    5     11/06/2020 15:26:30
hotels   hotel1   Night 9:00 pm    22    11/06/2020 08:47:50
hotels   hotel1   PM 3:00 pm        20   11/06/2020 08:47:40
hotels   hotel1   AM 7:00 am        22   11/06/2020 08:47:30
hotels   hotel1   PM 3:00 pm       20    11/06/2020 08:46:50
hotels   hotel1   AM 7:00 am       22    11/06/2020 08:46:40
hotels   hotel2   Tomorrow AM   5      11/06/2020 08:06:30
hotels   hotel2   PM 3:00 pm       4      11/06/2020 08:06:20
hotels   hotel2   AM 7:00 am       4      11/06/2020 08:06:10
hotels   hotel1   Night 9:00 pm   22    11/06/2020 07:13:50
hotels   hotel1   Night 9:00 pm   2      11/06/2020 07:13:40
hotels   hotel1   Night 9:00 pm   24    11/06/2020 07:13:30
hotels   hotel1   PM 3:00 pm      20    11/06/2020 07:13:20
hotels   hotel1   PM 3:00 pm      24    11/06/2020 07:13:10
hotels   hotel1   AM 7:00 am      22    11/06/2020 07:13:05
hotels   hotel1   Night 9:00 pm  24    11/06/2020 07:12:50
hotels   hotel1   PM 3:00 pm      24    11/06/2020 07:12:50
hotels   hotel1   AM 7:00 am      24    11/06/2020 07:12:40
hotels   hotel1   Night 9:00 pm   24   11/06/2020 07:12:30
hotels   hotel1   PM 3:00 pm      24    11/06/2020 07:12:20
hotels   hotel1   AM 7:00 am      24    11/06/2020 07:12:10
hotels   hotel2   AM 7:00 am      5      11/06/2020 01:00:50
hotels   hotel2   AM 7:00 am      6      11/06/2020 01:00:40
hotels   hotel2   Tomorrow AM  4      10/06/2020 08:37:50
hotels   hotel2   AM 7:00 am      6      10/06/2020 08:37:40
hotels   hotel2   Night 9:00 pm  5      10/06/2020 08:37:50
hotels   hotel2   Night 9:00 pm  6      10/06/2020 08:37:40
hotels   hotel2   AM 7:00 am     5      10/06/2020 08:37:30

I have tried formulas similar to these, and then some:

max('Sheet1 (2)'[ModifiedAt]) return maxx(FILTER('Sheet1',[ModifiedAt] = maxDate),'Sheet1'[Rooms])

Max Date = 
VAR CurrentName = 'Survey table'[Name]

RETURN
MAXX(
   FILTER( ALL( 'Survey table' ), 'Survey table'[Name] = CurrentName ),
        'Survey table'[Date Taken] )

But what I end up with is something like this:

HOTEL1 12/06/2020 08:46 4,200

HOTEL2 12/06/2020 07:38 468

instead, I want:

HOTEL1 12/06/2020 08:46 22

HOTEL2 12/06/2020 07:38 5

It's like its aggregating the room value for some reason. I'm losing my hair with this one. Please save my hair

[harshnathani]

Hi @cyberblitz ,

Check if your data types in Power Query is correct.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[cyberblitz]

@harshnathani 

Thanks. The datatypes are set correctly and still it doesn't work

[harshnathani]

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]

@harshnathani 

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

[harshnathani]

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]

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

[amitchandak]

@cyberblitz , refer if one of these can work

summarize(Table,table[Facility],LASTNONBLANKVALUE(Table[ModifiedAt],max(table[Rooms])))

or
summarize(Table,table[Facility],LASTNONBLANKVALUE(Table[ModifiedAt],LASTNONBLANKVALUE(Table[Shift],max(table[Rooms]))))

or
summarize(Table,table[Facility],LASTNONBLANKVALUE(Table[ModifiedAt],FIRSTNONBLANKVALUE(Table[Shift],max(table[Rooms]))))

[cyberblitz]

@amitchandak

I am afraid not. I get the error: Function SUMMARIZE expects a column name as argument number 4.

When i insert a column name in, i get the error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[amitchandak]

@cyberblitz , Try like

sumx(summarize(Table,table[Facility],"_1",LASTNONBLANKVALUE(Table[ModifiedAt],max(table[Rooms]))),[_1])
sumx(summarize(Table,table[Facility],"_1",LASTNONBLANKVALUE(Table[ModifiedAt],LASTNONBLANKVALUE(Table[Shift],max(table[Rooms])))) ,[_1])
sumx(summarize(Table,table[Facility],"_1",LASTNONBLANKVALUE(Table[ModifiedAt],FIRSTNONBLANKVALUE(Table[Shift],max(table[Rooms])))) ,[_1])

[cyberblitz]

@amitchandak 

I'm afraid this does not work either.

What i get is:

HOTEL1 12/06/2020 08:46 4,200

HOTEL2 12/06/2020 07:38 468

instead, I want this:

HOTEL1 12/06/2020 08:46 22

HOTEL2 12/06/2020 07:38 5

Everytime i apply these formulas, the value i need singled out is been aggreagated. This is so simple to do in SQL, why is so difficult using DAX??

[amitchandak]

@cyberblitz , provide data in table format. From excel copy paste on the word and from word to browser. Or upload excel dropbox or onedrive

[cyberblitz]

@amitchandak 

Sure thing: https://www.dropbox.com/s/ngvn2ff15iwymzd/Rooms.xlsx?dl=0

[v-eachen-msft · Accepted solution]

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]

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

[cyberblitz]

@v-eachen-msft

No, sorry.

The result should look exactly like:

FACILITY     MAX DATE                     VALUE

HOTEL1     12/06/2020 08:46:17      22

HOTEL2     12/06/2020 07:38:39      5

[v-eachen-msft]

Hi @cyberblitz ，

Sorry, I forgot to use a date type. 

Here is the result:

The measures are the two measures above.