Find a rolling sum for a specific moment in time while checking for most recent entry

[arhomberg]

Hello everyone,

I posted not too long ago about working to find the rolling sum with a snapshot in time within Power BI DAX.

Article: Solved: Re: Find a rolling sum for a specific moment in ti... - Microsoft Power BI Community

I have now run into the issue of entries being replicated as they are updated, and need to check and make sure that the entry being looked at is the most recent entry in time. This is checkable through a provided system moderator stamp.

In the table above, we would want to find the sum of the active transactions while ensuring that only the most recent version of the record is used (In this case, the MARR amount of 3 over 2 as 06/29/2021 is the more recent entry for Transaction 9).

So just as before I would do the follow as provided by @daxer-almighty 

Doing this will go through and make sure that at the specific date, the transaction is still considered to be open.

I am now unsure about how I would also filter the data to make sure the sum is only taking into account the most current version of the transaction.

Any advice on how to achieve this would be greatly appreciated!

[AlexisOlson]

One 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]
    )

[arhomberg]

@AlexisOlson 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 · Accepted solution]

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.