Another running total puzzle

[DCELL]

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.

[amitchandak]

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

@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 · Accepted solution]

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.

[DCELL]

@v-lionel-msft Your 2nd solution worked!! I had to remove the account column to get past the extra, double aggregation problem as you said

[v-lionel-msft]

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.

[parry2k]

@DCELL can you share pbix file, it is requesting for access.

[DCELL]

Hi @parry2k  try again now