Forum Discussion

Anonymous

Not applicable

7 years ago

Running total until Today

Hi guys, Below is the link for a sample data set where I'm trying to create a running total for actuals until current week where as I cannot seem to find a way to achieve this. The earlier posts ...

Continued Contributor

7 years ago

Maybe this can help in any way:

1. Goto Edit Queries and sort by date

2. Create in index column

3. Create a calculated column by:

Cumulative = CALCULATE(SUM(Data[Actual]);FILTER(Data;Data[Index]<EARLIER(Data[Index])-1))

But I see you have many rows without dates which makes me confused.

Anonymous
Not applicable
7 years ago
Hi Johanno,

Thank you for the below alternative but this one gives the 3.5million as actuals in value which should not be the case and I think this is because of the number of blanks i have in my dataset. The sample I shared is subset of data :(

Regards,
A!
- Johanno
  Continued Contributor
  7 years ago
  Ok, I don't fully understand. The sum of all actuals should be the last figure in the cumulative column.
  - Anonymous
    Not applicable
    7 years ago
    Hi Johanno,
    
    I want a cumulative value of actuals until the current week. However, the calculation provides the overall total value but does not breakdown per week value when plotted in line visual. I used the below mentioned measure to list me the values per week but gives me the actuals until the max value of date but I want the calculation to stop for the current week.
    
    Measure: Actuals show up for the entire time period when I use the below calculation where column [Yr | Wk - DueDate] is a combination of year and week #
    
    CALCULATE(
    SUM('tblEBV'[ACTUAL BV]),
    FILTER(
    ALLSELECTED('tblEBV'[Yr | Wk - DueDate]),
    ISONORAFTER('tblEBV'[Yr | Wk - DueDate], MIN('tblEBV'[Yr | Wk - DueDate]), DESC)
    )
    )
    
    Kind regards,
    A!