POWER PIVOT - Match Data as a common data query

Question

Hello,

I'm currently using power pivot in the context of an internal BI project. I've got a excel file where I agregate many datas from my company's tool and I send them into power pivot where I create and calculate indicator that I push on another excel file dedicated to the view in order to make the client aware of the situation of his domain, sector and so on.

Here is the problem that I currently have :

I'm got a tab into source excel file named Datagresso where I can find the ER by date and by project : the key are obviously the date and the project ID.

I've got the same tab into power pivot where I calculated the average ER, the total ER... I've linked the date with a dedicated date tab and I've done the same with a project tab which contains more information about my project.

I want to calculate the WIP (work in progress) in order to do that I need to get the last 3 months ER and that is where I encounter my problem I don't see a way to do that kind of query to pivot : 

Give me the ER Where (ProjectID = currentLineProjectID) AND (Date = Date(Year(currentLineDate);Month(currentLineDate)-1;Year(currentLineDate))

Thank you for your guidance !

Answer 1

You can accomplish this with a calculated measure that uses DATESINPERIOD to get the trailing 3 months like this:

CALCULATE(SUM(Datagresso[ER])
                        ,DATESINPERIOD(DimDate[FullDate]
                                                        ,LASTDATE(DimDate[FullDate])
                                                        ,-3, MONTH
                                                       )
                       )

And in your case, when you slice by project in a pivot, the calculation will pick up the current row's project and filter the measure by that.

Let me know if that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 2

You can accomplish this with a calculated measure that uses DATESINPERIOD to get the trailing 3 months like this:

CALCULATE(SUM(Datagresso[ER])
                        ,DATESINPERIOD(DimDate[FullDate]
                                                        ,LASTDATE(DimDate[FullDate])
                                                        ,-3, MONTH
                                                       )
                       )

And in your case, when you slice by project in a pivot, the calculation will pick up the current row's project and filter the measure by that.

Let me know if that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //

Thanks Brent for this reply, I was late to answer back sorry about that. The formula seems to be good but the result is inapropriate, I mean I put it as an indicator into power pivot (in the lines under the agregated datas). And as a result I get the exactly same value than the the ER value.

My goal more precisely is to calculate the WIP into my dashboard but not only the current wip but also the historical one that's why I need to get the 3 months from the current filter date

data agresso : imageshack.us/photo/my-images/707/screen12wp.png/

time table : imageshack.us/photo/my-images/195/screen11wk.png/

PS : i'm sorry I don't understand I can't up picture nor put link...

Regards