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