Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Running totals calculated column

Hi all,

 

I am trying to display the running totals per company per AfP period/month in a calculated table. My table was derived from the raw data as shown from the DAX formula below:

 

I created a new column Amount Accrual to insert my DAX formula for the cumulative total. The table is shown below enclosed in red box. After creating the new column, the DAX formula does not display the expected result. The expected result is found in the the right table enclosed in orange box. 

 

 

What could I fix in my formula so I can display my expected result? Or if it is not possible, can the expected result be done by making a virtual table?

Thank you for the help.

General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

    Please see the below

     

    Amount Accrual = 
VAR earlierAfP = Accruals1[AfP]
RETURN 
CALCULATE(
    SUM('Accruals1'[Company code Amount]),
    ALLEXCEPT('Accruals1', 'Accruals1'[Company Name]),
    Accruals1[AfP] <= earlierAfP -- you can use EARLIER() but this days VAR is preferred syntax
)

    Regards,
    Mariusz

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

1 Reply

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

    Please see the below

     

    Amount Accrual = 
VAR earlierAfP = Accruals1[AfP]
RETURN 
CALCULATE(
    SUM('Accruals1'[Company code Amount]),
    ALLEXCEPT('Accruals1', 'Accruals1'[Company Name]),
    Accruals1[AfP] <= earlierAfP -- you can use EARLIER() but this days VAR is preferred syntax
)

    Regards,
    Mariusz

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