Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total Issue for blank values

Hello, I have a table with date, amount,code,location. It has missing months, I have created a table that has all month data. I have related this table to solve the issue of the missing date. My amo...
Show and Tell
Tips and Tricks
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi Anonymous 

     

    Let me know if you'd like to get below desired running total:

     

    RunningTotal = CALCULATE(SUM(Table1[Value]),FILTER(ALL(Table1),[Date]<=MAX(Table1[Date])))

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello Ashish_Mathur  and v-diye-msft ,

Thanks for your responses. But I have still got 0 with both the formulas from DAX. Also, I have to display the value with dates in end of month format. All of this is also done.

I have missing dates on my main table. So for that, I have made a calendar table and built a relationship with the main table.
Now, I have also fixed the blanks with zero as mentioned earlier. But the missing month value is not changing the zero to last month's value. For example let say my data for 2015 has Jan, March, Aug, Sep, Oct, Nov and Dec.
Month  Amount  Running Total
JAN      70                 70
FEB       0                  0
MARCH  140             210
APRIL      0                   0
MAY        0                    0
JUNE       0                       0
JULY        0                     0
AUG     1900                2110
SEP       30                    2140
OCT    100                    2240
NOV  -1000                 1240
DEC    3000                  4240


Note 0 were blank in the Amount column, I have make them zero. But when I am doing running total with these missing dates then the missing month value is always 0 and not getting into account the last month value

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

My solution should work.  Share the link from where i can download your PBI file.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, Ashish_Mathur 

    For missing dates it is showing zero as the category/code column will be blank for the missing dates thus it is not calculating the running total according to the code.
    I can't share the file. For example, I have created the date table to get all the month-end value. Showing the sample for 1 activity/code below.I have around 10 codes. Hope I am clear with my requirement.

    CodeDateValueRunning totalDesired
    A1/31/2015101010
     2/28/2015 010
    A3/31/2015405050
     4/30/2015 050
     5/31/2015 050
     6/30/2015 050
    A7/31/201580130130
     8/31/2015 0130
    A9/30/201599011201120
    A10/31/2015-1000120120
    A11/30/2015-200-80-80
    A12/31/2015200019201920
    • v-diye-msft's avatar
      v-diye-msft
      Community Support
      6 years ago

      Hi Anonymous 

       

      Let me know if you'd like to get below desired running total:

       

      RunningTotal = CALCULATE(SUM(Table1[Value]),FILTER(ALL(Table1),[Date]<=MAX(Table1[Date])))

       

       

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      You have shared the desired output.  Now share some dummy data (in a format that can be pasted in an MS Excel workbook) to work with.