Hi Francis,

Do you want the cumulative total grouped by something, like Account?

Regards

Phil

I am not sure. But i am using the cumulative total to show the graph for budget vs actual.

It should also be able to show by total amount in y-axis & by month on x-axis which can be filtered by account, type or month.

Something like the attached.

But i am not getting the right figures, especially for my actuals.

At this time, i am using DAX in powerpivot. The results seems ok for budget but not for my actuals.

I am using calculated columns. I am using the same to calculate for my actuals.

Not sure if power query can helps with this.

Cumulative Budget

=CALCULATE(
SUM(Budget[Budget]),
FILTER(Budget,Budget[Date]<=EARLIER(Budget[Date]) &&
Budget[GL] = EARLIER(Budget[GL]) &&
Budget[Vessel] = EARLIER(Budget[Vessel])
)
)

Cumulative Commit

=CALCULATE(
SUM(Committed[Commit]),
FILTER(Committed,Committed[Date]<=EARLIER(Committed[Date]) &&
Committed[Cost Elem.]=EARLIER(Committed[Cost Elem.]) &&
Committed[Cost Ctr]=EARLIER(Committed[Cost Ctr])
)
)

Hi Francis,

I've moved this question to the Power Pivot forum because you shouldn't be calculating the running total in Power Query if you want to analyse the data in a PivotTable.

Why don't you use 'Show Values As' to set the field to calculate the running total? i.e. right-click one of the sum of budget cells in the PivotTable > show values as > Running total in... > Base field 'Date'.

Mynda

Thx alot. Guess i do not need that calculated columns after all.

I change Show Values as "Running Total In" against Base field "Month name" for both fields and it works fine.

On that graph which i have generated, is there a way to cut the line based on the availability of the transactions?

For example,

Budget - until year end (December)

Commit - until July 2020 (in the case of the example)

There should not be any more data point from the month of Aug onward for the Commit amount.

Hi Francis,

It's difficult to say without a sample file that shows whether your data for budget and committed are going to be in separate tables (as it would appear from your original formulas), and how they're related i.e. there should be a date/calendar table. However, this post should point you in the right direction.

Mynda

You are correct that i have two separate tables for budget and commit and i am using date table.

I created a sample file for this and i am still not getting the results which i want on the graph.

I can't even get the total commit amount correct with Show Value As > Running Total In.

Hi Franics,

I couldn't see any attempt at writing measures in your file. It's always best if you can show us what you've tried. That way you'll learn more than if we just do it for you.

In the attached file I've entered the running total measure. Note: I've renamed the Commit table 'Committed' because it's never a good idea to have a table with the same name as a column.

Mynda

The result seems to be not correct when i start to add more data.

The additional data ends on 20-Aug-2020.

It is not adding the August total in the running total column.

I am not sure how to change the measure to reflect this.

This is the measure formula which you have provided.

Commit RT:=VAR LastCommitDate = CALCULATE(LASTDATE('Committed'[Date]), ALL('Committed'))

RETURN
CALCULATE(SUM(Committed[Commit]),
FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])&& MAX('Calendar'[Date])<=LastCommitDate))

It seems that if i change the formula to this. It works fine. Please confirm.

Commit RT:=VAR LastCommitDate = CALCULATE(LASTDATE('Committed'[Date]), ALL('Committed'))

RETURN
CALCULATE(SUM(Committed[Commit]),
FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])&& MAX('Calendar'[Date])<=EOMONTH(LastCommitDate,0))

That makes sense if your actual data doesn't have anything on the last day of the month. Thanks for sharing.

If the budget amount ends on Mar, what do i need to add to the measure remove the remaining months so that the RT Line Chart will show correctly for Budget RT.

Is this the correct measure formula?

Budget RT:=VAR LastBudgetDate =

CALCULATE(

LASTDATE(Budget[Date]),

FILTER(Budget,Budget[Budget]<>0))

RETURN

CALCULATE(

SUM(Budget[Budget]),

FILTER(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date]) && MAX('Calendar'[Date])<=EOMONTH(LastBudgetDate,0)

))

Yes, that looks like it returns the correct results.