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] )
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]
)
Helper IAlexisOlson This is great, this is the logic I am looking to fulfil! However, when I implement this code, no results are being calculated.
- AlexisOlson
I don't think I can easily debug without having a file to work with. I had to make some assumptions regarding how you have things set up that may not be correct (e.g. I don't know that the mod stamp should be filtered by the same date variable or by something else). Hopefully, the concept of what I wrote is clear enough that you can make the necessary adjustment to apply to your case.