Hi All,

Not sure if this is the right forum, or if you even take Power Pivot questions, but I know this is a knowledgable group, so I'm going to give it a shot.

I have been working on creating measures to calculate data based on a 3rd table (ie - many-to-many relationship).

Here is the scenario:

Data is all being pulled from P6 database, except the PO list, which is supplied by the client. I want to measure performance against a specific contractor and their specific purchase orders, so I have tacked in a table with that information. My table relationships is shown below. In this case, I am working with the MOD_Site_ID, Q_SMonthly, and POFiltered tables, so ignore the Q_Site_Metrics table:

tables.JPG

Unfortunately, Power Pivot data models are only capable of a one-way relationship, but I have been able to create measures that allow me to filter by purchase orders, and here is how that works:

First, I create a measure on the category

AC Measure.JPG

Then I use that measure to create a second measure that will filter against the PO table

ACbyPOMeasure.JPG

So, that is working well. But the final step I'd like to take is to create a CUMULATIVE measure that can be filtered by PO. This allows me to chart the metric as it grows across the months. Here is an example of a cumulative measure works on another table in my project, and you can see that each successive row adds this months value to the row above it:

Finexample.JPG

I'd like to do that in my measure that filters by PO, but can't get it to work. Here is what is happening:

doesntworkhere.JPG

I've tried writing this measure several different ways, but no matter how I approach it, I haven't been able to solve it.
Here is the measure as it is written at the moment:
ACCumbyPO:=CALCULATE([ACbyPO],FILTER(ALL('Calendar'),[Date]<='Calendar'[Date]),VALUES('Calendar'[Year]))


Any DAX lovers out there willing to weigh in?