Monthly and cumulative calculation showing the same figure - what am I missing

abalserv

New Member
Joined
Oct 25, 2013
Messages
30
hi Folks
Got a powerpivot table (calendar table, linked to a date field in the transaction table).
Then got a list of accounts categories (e.g. Revenue, Cost of Goods Sold) That's in the Column labels.
I've got month pulled in from the calendar table on this as well.
In my Row labels I've got a list of unique project IDs
I've got two measures:
Total for Project PL: This is meant to show the monthly transactions for each category for each project ID
The measure I have for this is:
calculate(sum(transactions_table[net_accounted_X_FX_rates])).
Cumulative MTD for Project PL
Then for the cumulative MTD (intended to show the cumulative transactions to date for each project - they could go over a number of years)
=calculate([Total for Project PL],all(Calendar[Date]))
(intended to show all the transactions on that project for that account category to date). So really what I want the formula to do is check the month in the column label of the pivot table and only give me the transactions to that point.....

My problems
At the moment they are just showing the same figure...all the time for all categories. Whereas what I want them to show is the following:
Total for Project PL: just show monthly transactions for that project, for that account category
Cumulative MTD - to show the total cumulative transactions for that project, for that account category on that project.
Thanks again as always for your help..
Anne
 

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top