Forum Discussion

TKBI's avatar
TKBI
Frequent Visitor
2 years ago
Solved

Accumulate Column

Hello,    I have a list of contracts and am trying to figure out a way to accumulate the sum of active contracts over the year.    My Datatable looks something like this:  Counter   StartDa...
  • ValtteriN's avatar
    ValtteriN
    2 years ago

    Hi,

    For calculating running totals I recommend cheking this article: Computing running totals in DAX - SQLBI

    For your case here is an example:

    Column =
    VAR MaxDate = [Date] -- Saves the last visible date
    RETURN
        CALCULATE (
            SUM('Table (39)'[Counter]),            -- Computes sales amount
            'Table (39)'[Date] <= MaxDate,   -- Where date is before the last visible date
            ALL ( 'Table (39)')               -- Removes any other filters from Date
        )


    This is for a column like you requested, but I recommend using a measure. Also I recommend adding calendar table to your model. 


    End result:


    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

ValtteriN's avatar
ValtteriN
Community Champion
2 years ago

Hi,

For calculating running totals I recommend cheking this article: Computing running totals in DAX - SQLBI

For your case here is an example:

Column =
VAR MaxDate = [Date] -- Saves the last visible date
RETURN
    CALCULATE (
        SUM('Table (39)'[Counter]),            -- Computes sales amount
        'Table (39)'[Date] <= MaxDate,   -- Where date is before the last visible date
        ALL ( 'Table (39)')               -- Removes any other filters from Date
    )


This is for a column like you requested, but I recommend using a measure. Also I recommend adding calendar table to your model. 


End result:


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/