New PowerPivot user

[oXdanXo]

Hi guys,

I'm totally new to PowerPivot.
I have a few questions hopefully someone can answer.

Q1. Should I limit what I am bringing in in Excel or the query?
(I have a few tables that grow by approx. 2 million rows a month).

Q2. Is it possible to automatically filter the data so that when I refresh all of the PivotTables etc show only the last 12 months of data (Last day of the month) in 1 PivotTable and 3 months data (Daily) in the Other without adding a dummy column in the data?
(I can’t think of a way to do this without passing a parameter to the SQL and having 2 separate queries if I wasn’t using PowerPivot).

Q3. Can anyone see what the problem is with this DAX?
=OPENINGBALANCEMONTH(SUM(factAccount[Cash]),dimDate[FullDate],RELATED(dimAccount[AccountNumber])=dimAccount[AccountNumber])
(I have a cumulative measure and I want to figure out the difference between it taken in context of the filter (monthly or daily) values the previous value).

Thanks in advance for any advice,

Dan

 

[Matt Allington]

Q1. Should I limit what I am bringing in in Excel or the query?

yes. But limiting the columns is more important than limiting the rows. Only bring in the columns you need.

Q2. Is it possible to automatically filter the data so that when I refresh all of the PivotTables etc show only the last 12 months of data (Last day of the month) in 1 PivotTable and 3 months data (Daily) in the Other without adding a dummy column in the data?

this is probably not what you should do. Load all the data and use slicers to filter your data in the pivot tables

Q3. Can anyone see what the problem is with this DAX?
=OPENINGBALANCEMONTH(SUM(factAccount[Cash]),dimDate[FullDate],RELATED(dimAccount[AccountNumber])=dimAccount[AccountNumber])

this is a valid dax formula