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