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

[Phillegal]

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

[MarkLaf · Accepted solution]

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.

[Phillegal]

thanks, that worked!

[Praful_Potphode]

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

[Ahmed-Elfeel]

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.