Answered by:
Combined daily results in weeks
Question

Hi all,
I have a data set showing:
Date;FiscalWeekNumber,Results and another 20 metrics and segments on same row.
I have created a PowerPivot showing:
Date > in Column Labels
Sum of Results > Values
Measure 1 > Values
I want to display the % lift of results vs. previous week (days 7) and use the following DAX expression:
IFERROR(SUM(Query[Results]) / IF(CALCULATE(SUM(Query[Results]),Query[Date]) >= CALCULATE(SUM(Query[Results]),FIRSTDATE(Query[Date]+7)),CALCULATE(SUM(Query[Results]),DATEADD(Query[Date],7,DAY)),0)1,blank())
The query works perfectly fine and shows the exected results. However, as soon as I add the Fiscal week (column labels), the calculation is not performed anymore in my measure (for both days and Fiscal Weeks). I know that PowerPivots differ from standard pivots but I can't find a way around this and ensure that the caculation happens on both days / Fiscal weeks level.
Any thoughts,
Thanks in advance!
Tuesday, October 25, 2011 11:01 PM
Answers

Kman,
Would you mind sharing the workbook with some data? The solution is pretty easy to author, the longest part being the creation of a test dataset. If you provide some data to work on it will be much faster to give you an answer.
Anyway, it would be much better if you normalize your data model and create a calendar table, all time intelligence strongly relies on the existence of a calendar table. Lacking it, all the formulas will be much harder to write.
Alberto Ferrari
http://www.powerpivotworkshop.com Marked as answer by Challen Fu Thursday, November 3, 2011 1:51 AM
Wednesday, October 26, 2011 7:21 AM 
Hi Alberto,
Thanks for offering your help  but I wanted to spend a bit more time and see if I could figure it out (the hard way...).
I think I found out how to solve my problem by using the follwing:
=(
IFERROR(
(CALCULATE(SUM(EBD_BCK_END[RESULTS]),DATEADD(EBD_BCK_END[DT],7,DAY),
ALLEXCEPT(EBD_BCK_END,
EBD_BCK_END[DY],
EBD_BCK_END[CH],
EBD_BCK_END[REG1],
EBD_BCK_END[CO])))
,BLANK()))
/
(CALCULATE(SUM(EBD_BCK_END[RESULTS]))
)1My pivot is structure as follow on top (column labels) and want to display the results of the following structrure within a row:
01/01/11;01/02/11;01/03/11;01/04/11;01/05/11;01/06/11;01/07/11;TOTAL WEEK 1;01/08/11 etc....
The above provide me with the "% lift" of RESULTS vs. same day of previous week or for the total week the "% lift" vs. previous week. Not sure this is the best way to get there but I took me quite a while to understand this one ... I guess I need to get used a bit more on how PowerPivot work after working mainly with standard pivots in excel....
I hope this example may help others facing similar challenge.
Thanks,
Kman.
 Marked as answer by Challen Fu Thursday, November 3, 2011 1:51 AM
Monday, October 31, 2011 10:35 PM
All replies

Kman,
Would you mind sharing the workbook with some data? The solution is pretty easy to author, the longest part being the creation of a test dataset. If you provide some data to work on it will be much faster to give you an answer.
Anyway, it would be much better if you normalize your data model and create a calendar table, all time intelligence strongly relies on the existence of a calendar table. Lacking it, all the formulas will be much harder to write.
Alberto Ferrari
http://www.powerpivotworkshop.com Marked as answer by Challen Fu Thursday, November 3, 2011 1:51 AM
Wednesday, October 26, 2011 7:21 AM 
Hi Alberto,
Thanks for offering your help  but I wanted to spend a bit more time and see if I could figure it out (the hard way...).
I think I found out how to solve my problem by using the follwing:
=(
IFERROR(
(CALCULATE(SUM(EBD_BCK_END[RESULTS]),DATEADD(EBD_BCK_END[DT],7,DAY),
ALLEXCEPT(EBD_BCK_END,
EBD_BCK_END[DY],
EBD_BCK_END[CH],
EBD_BCK_END[REG1],
EBD_BCK_END[CO])))
,BLANK()))
/
(CALCULATE(SUM(EBD_BCK_END[RESULTS]))
)1My pivot is structure as follow on top (column labels) and want to display the results of the following structrure within a row:
01/01/11;01/02/11;01/03/11;01/04/11;01/05/11;01/06/11;01/07/11;TOTAL WEEK 1;01/08/11 etc....
The above provide me with the "% lift" of RESULTS vs. same day of previous week or for the total week the "% lift" vs. previous week. Not sure this is the best way to get there but I took me quite a while to understand this one ... I guess I need to get used a bit more on how PowerPivot work after working mainly with standard pivots in excel....
I hope this example may help others facing similar challenge.
Thanks,
Kman.
 Marked as answer by Challen Fu Thursday, November 3, 2011 1:51 AM
Monday, October 31, 2011 10:35 PM