PowerPivot

Resolved · Medium Priority · Version 2010

Thu 21 Apr 2016, 22:05 Delegate Paul J said...

Paul J has attended:
Excel PowerPivot course

PowerPivot

Hi,

Wondering how I can get powerpivot to replicate the following behavior.

I currently create reports and graphs which get their data from a static table which gets it's data via index,match on various pivottables (one PT for number of faults, another for SLA% etc). All these tables run from a rolling 14 periods which is calculated filed on all data tables. This is then used as a PT filter so when new data is bought in the refresh keeps the new 14 periods.

I am hoping that PP can allow me to create the measures needed to produce the graphs without the need for the static table.

Any advice on how to set up the measures etc that would allow for this kind of auto selection and rolling data.

I have a linked table from my main excel spreadsheet which will be my rolling 14 periods. Should I add a calculation here whereby it calculates(sumx(filter())) the main data within a column here then I use this else where...

Thanks in advance

Mon 25 Apr 2016, 09:49 Trainer Jens said...

RE: PowerPivot

Hi Paul,

Thank you for the forum question.

Yes a combination using Calculate & SumX should be able to do the job.You will not need the Filter function because the Calculate function can work with any number of filters. In the Calculate function's filter argument you can use the Date functions (Month, DateAdd, Year) to define how many periods you will want to include in the measure.

The Calculate function together with Sumx is a very powerful combination and they works like a super charged Sumifs.

I hope this can point you in the right direction. Specially the DateAdd function can be helpful. It can work with years, quarters, months, weeks, days, hours, minutes and seconds.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 2 May 2016: Automatically marked as resolved.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Recovering Unsaved Work in Excel 2010

Ever closed your Excel workbook in a hurry without saving your work beforehand? Here's how to get it back.

Choose File then Info, click Manage Versions and then Recover Unsaved Workbooks which will automatically find the spreadsheets that haven't been saved.

View all Excel hints and tips