Forum Discussion

Anonymous's avatar
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 ...
Need Help
Show and Tell
Johanno's avatar
Johanno
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's avatar
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's avatar
    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's avatar
      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!