topic Re: SUM newest value by ID in selected timeframe and use last available value if blank in DAX Commands and Tips

SUM newest value by ID in selected timeframe and use last available value if blank

Phillegal — Fri, 07 Nov 2025 14:04:31 GMT

Hello,

I am currently struggling with one of my reports. My data source is a sharepoint list and I managed to make it work to get the version history, so every time someone makes a change to one sharepoint element I get a new row in my dataset.
My colleagues not only want to see the newest data (I already made that work) but they also want to see what information was valid one or two month ago. The problem is that the data doesnt change in regular time intervals.

So I am looking for a dax formula that returns the most recent date&time in a selected reporting timframe (selected Month X) for each item ID and then sums up the values. If there is no value for the selected timeframe it should use the last availbe entry for that ID.

The dataset somewhat looks like this table:

The slicer for the timeframe looks like this:

So if I select September 2025 I want to sum up "12" as the amount of available staff for ID 1 and "25" for ID 2 as there is no value for September for ID 2, so the result should be 37.

And if I select Oktober 2025 I want to make sure to take the newest entry for ID 2 as there can be multiple entrys for the same day and the value could be higher or lower, so the result should be 40.

Of course this is a very simplified version of my actual dataset. I really hope someone has an idea.
KR Philipp

Re: SUM newest value by ID in selected timeframe and use last available value if blank

Ahmed-Elfeel — Fri, 07 Nov 2025 14:49:06 GMT

Hi Phillegal,

Here is the DAX measure that should solve your problem:

Total Staff Selected Period = VAR SelectedDate = SELECTEDVALUE('Report Date'[Report date]) VAR SelectedMonthStart = EOMONTH(SelectedDate, -1) + 1 // First day of selected month VAR SelectedMonthEnd = EOMONTH(SelectedDate, 0) // Last day of selected month // Get all IDs that exist in the dataset VAR AllIDs = VALUES(YourTable[ID]) // For each ID find the most recent entry within or before the selected month VAR LatestEntriesPerID = ADDCOLUMNS( AllIDs, "LatestValidDate", CALCULATE( MAX(YourTable[Modified]), YourTable[Modified] <= SelectedMonthEnd, ALL(YourTable) ) ) // For each ID with the latest date, get the most recent time and corresponding staff VAR LatestStaffPerID = ADDCOLUMNS( LatestEntriesPerID, "LatestStaff", VAR CurrentID = [ID] VAR CurrentLatestDate = [LatestValidDate] RETURN CALCULATE( MAX(YourTable[staff]), FILTER( YourTable, YourTable[ID] = CurrentID && YourTable[Modified] = CurrentLatestDate && YourTable[Modified - time] = CALCULATE( MAX(YourTable[Modified - time]), YourTable[ID] = CurrentID, YourTable[Modified] = CurrentLatestDate ) ) ) ) RETURN SUMX(LatestStaffPerID, [LatestStaff])

Also you can use this approach:

Total Staff Selected Period v2 = VAR SelectedDate = SELECTEDVALUE('Report Date'[Report date]) VAR SelectedMonthEnd = EOMONTH(SelectedDate, 0) RETURN SUMX( VALUES(YourTable[ID]), VAR CurrentID = [ID] VAR LatestDateInPeriod = CALCULATE( MAX(YourTable[Modified]), YourTable[Modified] <= SelectedMonthEnd, ALLEXCEPT(YourTable, YourTable[ID]) ) VAR LatestTimeOnDate = CALCULATE( MAX(YourTable[Modified - time]), YourTable[Modified] = LatestDateInPeriod, ALLEXCEPT(YourTable, YourTable[ID]) ) RETURN CALCULATE( MAX(YourTable[staff]), YourTable[Modified] = LatestDateInPeriod, YourTable[Modified - time] = LatestTimeOnDate, ALLEXCEPT(YourTable, YourTable[ID]) ) )

Uses EOMONTH to get the start and end of the selected month
For each ID finds the most recent entry on or before the end of the selected month
Also when multiple entries exist on the same day it takes the one with the latest time
If no entry exists in the selected month it automatically uses the most recent previous entry

Make Sure:

Your Report Date table should have proper date values (not just text)
Your Modified column should be a proper date type
Your Modified - time column should be a proper time type

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: SUM newest value by ID in selected timeframe and use last available value if blank

Praful_Potphode — Fri, 07 Nov 2025 18:02:52 GMT

Hi Phillegal

try below measure:

Available Staff = VAR SelectedDate = SELECTEDVALUE('DateTable'[Date]) VAR SelectedYearMonth = YEAR(SelectedDate) * 100 + MONTH(SelectedDate) RETURN SUMX( VALUES('StaffTable'[ID]), VAR CurrentID = 'StaffTable'[ID] VAR LatestStaff = CALCULATE( LASTNONBLANK('StaffTable'[staff], 1), FILTER( ALL('StaffTable'), 'StaffTable'[ID] = CurrentID && (YEAR('StaffTable'[Modified]) * 100 + MONTH('StaffTable'[Modified])) <= SelectedYearMonth ), LASTDATE('StaffTable'[Modified]) ) RETURN LatestStaff )

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Re: SUM newest value by ID in selected timeframe and use last available value if blank

MarkLaf — Fri, 07 Nov 2025 20:38:15 GMT

I'm assuming you have a separate Dates dimension being used in the slicer.

You can use SUMX to iterate over IDs and INDEX to grab the latest staff # based on Modified DESC, Modified - time DESC.

Sample data I used, copied from your snips.

staffing

version	Modified	Modified - time	ID	staff
1.0	8/31/2025	0.08:00:50	1	10
2.0	9/5/2025	0.10:00:00	1	12
1.0	8/31/2025	0.11:20:00	2	25
2.0	10/8/2025	0.08:30:40	2	31
3.0	10/8/2025	0.09:00:00	2	31

Dates

Here is the measure I described at top.

Historical Staffing = VAR _maxDt = CALCULATE( MAX( Dates[Date] ), ALLSELECTED( Dates ) ) RETURN CALCULATE( SUMX( VALUES( staffing[ID] ), VAR _latestRow = CALCULATETABLE( INDEX( 1, ORDERBY( staffing[Modified], DESC, staffing[Modified - time], DESC ), PARTITIONBY( staffing[ID] ) ), staffing[Modified] <= _maxDt ) RETURN CALCULATE( SUM( staffing[staff] ), _latestRow ) ), REMOVEFILTERS( Dates ) )

Note: intellisense doesn't like INDEX without the relation arg, but it is valid as is (defaults to ALLSELECT of columns in ORDERBY And PARTITIONBY).

Snip of measure in action.

Re: SUM newest value by ID in selected timeframe and use last available value if blank

Phillegal — Mon, 10 Nov 2025 11:27:46 GMT

thanks, that worked!