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 UserOne approach would be to add a calculated column that checks if the row is the latest version or not and then use that as a filter in your measure.
IsCurrent =
IF (
T[System Mod Stamp]
= CALCULATE (
MAX ( T[System Mod Stamp] ),
ALLEXCEPT ( T, T[Transaction No. (ID)] )
),
1,
0
)
Then you modify the measure to be:
[Total MRR] =
VAR LastVisibleDate = MAX ( Dates[Date] )
RETURN
SUMX (
FILTER (
T,
T[IsCurrent] = 1
&& 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.
- AlexisOlson
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] )- arhomberg
AlexisOlson 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.