Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Running total

I am trying to get a calculate a running total per vendor. I am wanting to have each month sum all the previous months.  For example, a date range from Jan 2018 - Aug 2018. I want to know for a spec...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    8 years ago

    Hi Anonymous,

    Based on my test, you could refer to below formula:

    running total measure = CALCULATE(SUM(Table1[Sales]),
                                           FILTER(ALL(Table1),
                                               'Table1'[Date] <= MAX('Table1'[Date])
                                               &&
                                                   YEAR('Table1'[Date]) = YEAR(MAX('Table1'[Date]))))

    Result:

    You could download the pbix file to have a view, if the problem still occurs, could you please offer me some sample data to test?

    https://www.dropbox.com/s/yitavv5yxuxt418/Running%20total.pbix?dl=0

     

    Regards,

    Daniel He

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous Thank you for your response. 

 

Here is my graph. What I am trying to get it to do is calculate the sales in June, then in July has the sales from June be added to the sales in July. Then in Aug have the sales from Jun & Jul added to Aug.

 

Maybe I didn't explain the issue in my first post clearly and I apologize. But that is what I am trying to do. I may have the correct formula but I am not sure how to format my graph to show the way I am looking for it to show. I am also new to power bi and still learning as I go. Thanks for all the help

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous I understood what you are trying to do, could you share your power bi file so I can see what you are doing of it?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    try something like this

     

    CurrentSales running total in FormatMM =
    CALCULATE(
    SUM('P3: Previous Month Sales'[CurrentSales]),
    FILTER(
    ALLSELECTED('P3: DimDate'[FormatMM]),
    ISONORAFTER('P3: DimDate'[FormatMM], MAX('P3: DimDate'[FormatMM]), DESC)
    )
    )