Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 amount has blank values if I for a few "code" in a few months. I have fixed it by a measure  = IF(SUM('Table'[ Amount])=BLANK(),SUM('Table'[Amount])+0,SUM('Table'[Amount]))

Now my requirement is to calculate the running total and if the values of any month are blank(now zero as I replace the blank with zero) then I want that it will show the last month value.
I am creating a table visual with code, amount and date(this is from the date table that is created to fill missing months)
Running Total =CALCULATE([measure],

FILTER(ALLSELECTED('dT'[EOM]),
'dT'[EOM]<=MAX('dT'[EOM])))
But it is showing only zero for all the missing months and not generating the last month's values for the zero. Rest it is working fine and calculating the running total for all the months that have some value.

Basically, I want if Jan has 60 for a particular account and Feb has 0 or blank for that account then I want Feb to show 60 as well.
This is broken up based on a code column. I hope I am clear in explanation. I have tried many solutions, but nothing worked.
Can anyone sort this one for me?
1 ACCEPTED SOLUTION
Community Support

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])))``

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
7 REPLIES 7
Super User

Hi,

Create a Calendar Table and build a relationship from the Date column of your Table to the Date column of the Calendar Table.  In the Calendar Table, create 2 calculated column formulas to extract Year and Month

Year = Year(Calendar[Date])

Month Name = FORMAT(Calendar[Date],"mmmm")

Month Number = MONTH(Calendar[Date])

Click on any cell in the Month Name column and go to Sort By Column > Month Number.

To your visual, drag Year and Month from the Calendar Table.

Write these measures:

Measure = SUM('Table'[Amount])

Running Total = CALCULATE([Measure],DATESYTD(Calendar[Date],"31/12"))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

 Code Date Value Running total Desired A 1/31/2015 10 10 10 2/28/2015 0 10 A 3/31/2015 40 50 50 4/30/2015 0 50 5/31/2015 0 50 6/30/2015 0 50 A 7/31/2015 80 130 130 8/31/2015 0 130 A 9/30/2015 990 1120 1120 A 10/31/2015 -1000 120 120 A 11/30/2015 -200 -80 -80 A 12/31/2015 2000 1920 1920
Community Support

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])))``

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support

Hi @Anonymous

If you'd like to show the blank value as the last value, there're 2 methods:

1. Using Fill down in power query:

2. Using the measure below:

``````Measure = var a = CALCULATE(MAX('Table'[Month]),FILTER(ALL('Table'),[Month]<MAX('Table'[Month])&&[Amount]<>BLANK()))
var b = CALCULATE(MAX('Table'[Amount]),FILTER(ALL('Table'),[Month]=a))
Return
IF(MAX('Table'[Amount])=BLANK()||MAX('Table'[Amount])=0,b,MAX('Table'[Amount])
)``````

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors