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

     

     

v-diye-msft's avatar
v-diye-msft
Community Support
6 years ago

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