PBIX: https://drive.google.com/file/d/1qQPbUKYFdpr1FMSLQ5Xf5lMfMszB_8yl/view?usp=sharingEXCEL: https://drive.google.com/file/d/1WdpHyK54pwY99za2YObX0fCc8tV_QYYB/view?usp=sharingHi!I've looked at more than 10 different posts with similar problems, but the solutions don't seem to work for my dataset. I made a sample set that has the same layout as my actual dataset. I have tried many variations of this code:Running Test = CALCULATE(SUM('Sheet2'[amt]),FILTER(ALL('Sheet2'),'Sheet2'[Date] <= EARLIER('Sheet2'[Date] ) && 'Sheet2'[Country] = EARLIER('Sheet2'[Country] )&& 'Sheet2'[account] = EARLIER('Sheet2'[account] )))with using ALL vs ALLSELECT, EARLIER( [Date] ) vs. MAX( [Date] ), specifying ALL('Sheet2'[Date]), plus combinations of commenting out the [account] and [country] columns. I still don't get the right total in my visual.It supposed to display the running total by country over time in a stacked column chart, but not by account number, something like this: But the numbers don't line up between pbi and excel. Filtering the Excel sheet for ukr only, for example, gives a total of -2,463,006.92 for the sum of the amounts, while the Power BI visual gives all sorts of numbers.

Hi DCELL , Or What you want is this? Measure = CALCULATE( SUM(Sheet2[amt]), FILTER( ALL(Sheet2), Sheet2[Country] = MAX(Sheet2[Country])&& Sheet2[Date] <= MAX(Sheet2[Date]) ) ) Best regards,Lionel Chen If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

DCELL

Resolver I

6 years ago

Solved

Another running total puzzle

PBIX: https://drive.google.com/file/d/1qQPbUKYFdpr1FMSLQ5Xf5lMfMszB_8yl/view?usp=sharing
EXCEL: https://drive.google.com/file/d/1WdpHyK54pwY99za2YObX0fCc8tV_QYYB/view?usp=sharing

Hi!
I've looked at more than 10 different posts with similar problems, but the solutions don't seem to work for my dataset. I made a sample set that has the same layout as my actual dataset. I have tried many variations of this code:

Running Test =

CALCULATE(

SUM('Sheet2'[amt]),

FILTER(ALL('Sheet2'),

'Sheet2'[Date] <= EARLIER('Sheet2'[Date] )

&& 'Sheet2'[Country] = EARLIER('Sheet2'[Country] )

&& 'Sheet2'[account] = EARLIER('Sheet2'[account] )

))

with using ALL vs ALLSELECT, EARLIER( [Date] ) vs. MAX( [Date] ), specifying ALL('Sheet2'[Date]), plus combinations of commenting out the [account] and [country] columns. I still don't get the right total in my visual.
It supposed to display the running total by country over time in a stacked column chart, but not by account number, something like this:

But the numbers don't line up between pbi and excel. Filtering the Excel sheet for ukr only, for example, gives a total of -2,463,006.92 for the sum of the amounts, while the Power BI visual gives all sorts of numbers.

Need Help

v-lionel-msft
6 years ago
Hi DCELL ,

Or What you want is this?

Measure = CALCULATE( SUM(Sheet2[amt]), FILTER( ALL(Sheet2), Sheet2[Country] = MAX(Sheet2[Country])&& Sheet2[Date] <= MAX(Sheet2[Date]) ) )

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

parry2k
Super User
6 years ago
DCELL can you share pbix file, it is requesting for access.
- DCELL
  Resolver I
  6 years ago
  Hi parry2k try again now
amitchandak
Super User
6 years ago
DCELL , are you trying for column or measure . This will work for column

Running Test = CALCULATE( SUM('Sheet2'[amt]), FILTER(ALL('Sheet2'), 'Sheet2'[Date] <= EARLIER('Sheet2'[Date] ) && 'Sheet2'[Country] = EARLIER('Sheet2'[Country] ) && 'Sheet2'[account] = EARLIER('Sheet2'[account] ) ))

Try for measure

Running Test = CALCULATE( SUM('Sheet2'[amt]), FILTER(ALL('Sheet2'), 'Sheet2'[Date] <= max('Sheet2'[Date] ) && 'Sheet2'[Country] = max('Sheet2'[Country] ) && 'Sheet2'[account] = max('Sheet2'[account] ) ))
- DCELL
  Resolver I
  6 years ago
  amitchandak , I think in my case a column would be better, but whichever one works, works.
  I tried the column and the measure codes you provided but interestingly both gave different totals on the visual.
  - v-lionel-msft
    Community Support
    6 years ago
    Hi DCELL ,
    
    According to your formula, I think what you want is the value of [measure] above, so you must add the above 4 fields in your visuals, otherwise the data will be aggregated. This is true even if you create a calculated column.
    
    Maybe you can use a matrix?
    
    Best regards,
    Lionel Chen
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.