Carry over the amount from prior days

[jvilocura]

Hello everyone, 

I'm here to ask for your assistance on how can I carry over the amount from the last transaction date?
For example, 11/26/2024 amt will carry over to let's say 12/02/2024. Is that possible?

The dataset or table below has one-to-many relationship with my Date table.

If you need anything, please let me know. I'm quite new to this.

Thank you in advance!

[Anonymous · Accepted solution]

Thanks for the reply from FreemanZ , please allow me to provide another insight:

Hi, @jvilocura 

Regarding your question, I have created the following sample data:

Secondly, I created the following calculated column in my date table:

Column =
IF (
    'my Date'[Date] = DATE ( 2024, 12, 2 ),
    CALCULATE (
        SUM ( 'Table'[Amt] ),
        FILTER ( 'Table', 'Table'[Date] = DATE ( 2024, 11, 26 ) )
    )
)

Below is the final result:

Of course, if there are multiple dates that need to be carried forward, you can also try using the SWITCH() function. Here is a simple example:

Column2 =
SWITCH (
    'my Date'[Date],
    DATE ( 2024, 12, 2 ),
        CALCULATE (
            SUM ( 'Table'[Amt] ),
            FILTER ( 'Table', 'Table'[Date] = DATE ( 2024, 11, 26 ) )
        ),
    DATE ( 2024, 12, 3 ),
        CALCULATE (
            SUM ( 'Table'[Amt] ),
            FILTER ( 'Table', 'Table'[Date] = DATE ( 2024, 11, 25 ) )
        ),
    BLANK ()
)

Here's my final result, which I hope meets your requirements.

Here is the relevant documentation:

 SWITCH function (DAX) - DAX | Microsoft Learn

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @jvilocura ,

it is more advisible to have daily amount in the data table. 

With measure (or calculated column, intuitive but really adivisible), it is easy to do cumulative calculations.