Forum Discussion
Post PatronMeasure for MAX Date, for Every Record
Hello, I have two columns.
Schedule[Date] and Schedule[Job],
I'm trying to create a measure that finds the MAX [Date] for every record in [Job]. Some of the formulas I've tried (and no longer remember) seemed to bring back some records, but I think it was only the maximum record per date, whereas I need the maximum record per date, per job.
My apologies. I misunderstood what you were after (I though you only wanted one name based on the last modified date). See if this measure returns what you expect (even in Direct Query?)
Latest Job by date = VAR _MaxDateTime = CALCULATE ( MAX ( 'Main Table'[ModifiedDate] ), FILTER ( ALLSELECTED ( 'Main Table' ), 'Main Table'[Name] = MAX ( 'Main Table'[Name] ) ) ) RETURN CALCULATE ( MAX ( 'Main Table'[Job] ), FILTER ( 'Main Table', 'Main Table'[ModifiedDate] = _MaxDateTime ) )Thinkg about it, I don't think my last suggestion delivers what you need, since it shows the last job by name, but ignores that las modified date.
So see if this measure returns the job by last modified date:
Latest job by modified date = VAR _MaxDateTime = CALCULATE ( MAX ( 'Main Table'[ModifiedDate] ), FILTER ( ALLSELECTED ( 'Main Table' ), 'Main Table'[Job] = MAX ( 'Main Table'[job] ) ) ) RETURN CALCULATE ( MAX ( 'Main Table'[Job] ), FILTER ( 'Main Table', 'Main Table'[ModifiedDate] = _MaxDateTime ) )
28 Replies
patri0t82This formula:
MAX JobAbbr = VAR Job = SELECTEDVALUE( MyTable[Job] ) RETURN MAXX( FILTER( MyTable, MyTable[JobAbbr] = Job ), MyTable[Date] )Appears to almost work, but it's bringing back the MAX Date, and not the Job for that Date
PaulDBrownCommunity Champion
Can you provide a depiction of the expected result?
- patri0t82
Hello again, the SQL solution apparently wasn't a solution after all.. anyway, I'll attempt to be more clear here. My apologies.
The code I'm trying now is:
MAX JobAbbr = VAR Job = SELECTEDVALUE( MyTable[Job] ) RETURN MAXX( FILTER( MyTable, MyTable[Job] = Job ), MyTable[ModifiedDate] )this is what it is returning in my Matrix:
This last picture should really help identify what the problem is:
As you can see, both people were originally scheduled to work on 11/20/2022, however a newer record was created for Bill, who replaced Adam.
In my matrix on Bill's row, I need to show MF beside Bill's name for 11/20, not Adam's name. And it has to show the Job, not the DateModified.
Hopefully that makes sense.. sorry again, I really appreciate the help.
- patri0t82
I think I'm leaving some data out. My apologies.
I have CalendarTable[Date] which is connected to MyTable[Date]
There's another column called MyTable[ModifiedDate], which is a more specific datetimestamp.
If I have Job1 posted in MyTable[Job] more than once on the same Date, I need to pull the one with the most recent [ModifiedDate]
v-jianboli-msftCommunity Support
Hi patri0t82 ,
Based on your description, I have created a simple sample:
Please try:
Measure = MAX('MyTable'[ModifiedDate])Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- patri0t82
Thank you for the response. Unfortunately I need to place it in a matrix to show the result from JobAbbr
- v-jianboli-msft
Hi patri0t82 ,
Based on your description, it seems that you want to first filter for the job corresponding to the most recent modification date and then get the name of the person corresponding to that job?
If so, please try:
Measure = VAR _a = CALCULATE ( MAX ( 'Table1'[ModifiedDate] ), FILTER ( ALL ( Table1 ), [Area] = SELECTEDVALUE ( Table1[Area] ) && [ScheduleDate] = SELECTEDVALUE ( Table1[ScheduleDate] ) ) ) VAR _b = CALCULATE ( MAX ( 'Table1'[Job] ), FILTER ( ALL ( Table1 ), [ModifiedDate] = _a && [Area] = SELECTEDVALUE ( Table1[Area] ) && [ScheduleDate] = SELECTEDVALUE ( Table1[ScheduleDate] ) ) ) VAR _c = SELECTCOLUMNS ( 'Table1', "Job", [Job] ) RETURN IF ( _b IN _c, _b, BLANK () )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- patri0t82
Thank you for the response. You are correct, however, in your example, you have selected on November 22 for Area1 and are only showing 3 people, and there should be 9.
- patri0t82
Thank you everyone for your help with this issue. Unfortunately I'm not sure a solution is readily available, at least for use with Direct Query.
I've switched to import mode and the following code appears to provide what I'm looking for in Power Query:
let Source = MySource, #"Grouped Rows" = Table.Group(Source, {"ScheduleDate", "Shift", "Area", "Job", "JobAbbr"}, {{"AllRows", each _, type table [ScheduleDate=nullable datetime, Name=text, Shift=nullable text, Area=nullable text, Job=nullable text, CalculatedDate=nullable number, DateTimeRecordAdded=datetime, CreatedByUser=nullable text, JobAbbr=nullable text, colScheduleDate=nullable datetime]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([AllRows],"DateTimeRecordAdded")), #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Name", "DateTimeRecordAdded"}, {"Name", "DateTimeRecordAdded"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllRows"}) in #"Removed Columns"- PaulDBrown
My apologies. I misunderstood what you were after (I though you only wanted one name based on the last modified date). See if this measure returns what you expect (even in Direct Query?)
Latest Job by date = VAR _MaxDateTime = CALCULATE ( MAX ( 'Main Table'[ModifiedDate] ), FILTER ( ALLSELECTED ( 'Main Table' ), 'Main Table'[Name] = MAX ( 'Main Table'[Name] ) ) ) RETURN CALCULATE ( MAX ( 'Main Table'[Job] ), FILTER ( 'Main Table', 'Main Table'[ModifiedDate] = _MaxDateTime ) )- patri0t82
Thank you!