Forum Discussion
Helper IFind a rolling sum for a specific moment in time while checking for most recent entry
So you aren't looking for the most recent but the most recent up to a particular (dynamically determined) date? In that case, a calculated column is indeed not flexible enough but you can adapt similar logic.
If you want to take the rows with the last modification up to LastVisibleDate, then you can combine the expressions above into something like this:
[Total MRR] = VAR LastVisibleDate = MAX ( Dates[Date] ) RETURN SUMX ( FILTER ( T, T[System Mod Stamp] = CALCULATE ( MAX ( T[System Mod Stamp] ), FILTER ( ALLEXCEPT ( T, T[Transaction No. (ID)] ), T[System Mod Stamp] <= LastVisibleDate ) ) && T[Start Date] <= LastVisibleDate && LastVisibleDate <= T[End Date] ), T[MRR Amount] )
Helper IAlexisOlson Thanks for the response. I tried this solution, but the problem that I am running into with this is that it does not hold up for snapshots in time (which is what the main sum formula is looking to do). When running the sum formula, it will ommit all those labeled 0, even if at that specific time the record was actually the most recent. Does this make sense? I would need to adapt this into the function so that as the function loops through time, so does the validation of what is avilable and what is most recent.
Super UserSo you aren't looking for the most recent but the most recent up to a particular (dynamically determined) date? In that case, a calculated column is indeed not flexible enough but you can adapt similar logic.
If you want to take the rows with the last modification up to LastVisibleDate, then you can combine the expressions above into something like this:
[Total MRR] =
VAR LastVisibleDate = MAX ( Dates[Date] )
RETURN
SUMX (
FILTER (
T,
T[System Mod Stamp]
= CALCULATE (
MAX ( T[System Mod Stamp] ),
FILTER (
ALLEXCEPT ( T, T[Transaction No. (ID)] ),
T[System Mod Stamp] <= LastVisibleDate
)
)
&& T[Start Date] <= LastVisibleDate
&& LastVisibleDate <= T[End Date]
),
T[MRR Amount]
)