Forum Discussion
Helper IIFind 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
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
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] ) )
16 Replies
- amitchandak
Super User
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
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])
Hi cyberblitz ,
Check if your data types in Power Query is correct.
Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
- cyberblitz
Anonymous
Thanks. The datatypes are set correctly and still it doesn't work
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
