I have been working on this sales report on and off for several months. I need to recreate a manual excel report that compares month over month sales by workday for manufacturing purposes. </SPAN>
I modeled my original powerpivot report using workdays, but soon found that I was trying to imitate the existing DATE functions in powerpivot. After trying to jam a square through a circular hole several times, I decided to change my model and base the calculations off of the actual calendar days in order to leverage the DATE calculations.</SPAN>
Several of the calculations have been straightforward as anticipated, but my month over month calculation by day has proven difficult.</SPAN>
Below is my powerpivot table, I have changed the first two columns easily:</SPAN>
MTDSales =TotalMTD(sum(SalesData[Nets Sales]),Calendar[FormatDate])</SPAN>
CurrentDailySales =calculate (sum (SalesData[Nets Sales]))</SPAN>
PrevMonthDay = skip now will elaborate below;</SPAN>
PrevMonth2 =sumx(values(Calendar[Workday]),calculate(calculate(sum(SalesData[Nets Sales]),parallelperiod(Calendar[FormatDate],2,month),filter(all(Calendar[Workday]),Calendar[Workday]=max(Calendar[Workday])))))</SPAN>
This is still based off of the workday, but it gives me the previous month’s sales in a row context. My main issue is that my 0 workdays (weekends & holidays) were displaying the sum of all 0’s on each row. The grand total was correct, but when expanded it was misleading on a daily basis (see table note (1)). I received some help with this one, but was obviously inspired that day. I don’t completely understand how it works because when I tried to correct it, I couldn’t.</SPAN>
Back to PrevMonthDay = calculate (sum (SalesData[Nets Sales]),parallelperiod(Calendar[FormatDate],-1,month))
</SPAN>
This results in the total sum of the prior month being repeated in each row (see table note (2)). I tried tweaking my original formula (PrevMonth2 above) that semi-worked on the workdays, but every row came back blank:</SPAN>
=sumx(values(Calendar[FormatDate]),calculate(calculate(sum(SalesData[Nets Sales]),parallelperiod(Calendar[FormatDate],-1,month), filter(all(Calendar[FormatDate]), Calendar[FormatDate]=max(Calendar[FormatDate])))))</SPAN>
So I tried, DATEADD:</SPAN>
=calculate (sum(SalesData[Nets Sales]),dateadd(Calendar[FormatDate],-30,day)) and that yielded:</SPAN>
ERROR – CALCULATION ABORTED: MdxScript(Sandbox) (22,88) Function ‘DATEADD’ only works with contiguous date selections.
Not sure why this common function was not included in the date functions (or is it?) but it can't be this hard to figure out. Please note, I am a business analyst proficient in excel and access, but my dev language skills are not up to par, so please provide a "dummy" explanation if possible.
Thanks in advance for your help, and please let me know if you need any more information!
<TBODY>
</TBODY></SPAN>
I modeled my original powerpivot report using workdays, but soon found that I was trying to imitate the existing DATE functions in powerpivot. After trying to jam a square through a circular hole several times, I decided to change my model and base the calculations off of the actual calendar days in order to leverage the DATE calculations.</SPAN>
Several of the calculations have been straightforward as anticipated, but my month over month calculation by day has proven difficult.</SPAN>
Below is my powerpivot table, I have changed the first two columns easily:</SPAN>
MTDSales =TotalMTD(sum(SalesData[Nets Sales]),Calendar[FormatDate])</SPAN>
CurrentDailySales =calculate (sum (SalesData[Nets Sales]))</SPAN>
PrevMonthDay = skip now will elaborate below;</SPAN>
PrevMonth2 =sumx(values(Calendar[Workday]),calculate(calculate(sum(SalesData[Nets Sales]),parallelperiod(Calendar[FormatDate],2,month),filter(all(Calendar[Workday]),Calendar[Workday]=max(Calendar[Workday])))))</SPAN>
This is still based off of the workday, but it gives me the previous month’s sales in a row context. My main issue is that my 0 workdays (weekends & holidays) were displaying the sum of all 0’s on each row. The grand total was correct, but when expanded it was misleading on a daily basis (see table note (1)). I received some help with this one, but was obviously inspired that day. I don’t completely understand how it works because when I tried to correct it, I couldn’t.</SPAN>
Back to PrevMonthDay = calculate (sum (SalesData[Nets Sales]),parallelperiod(Calendar[FormatDate],-1,month))
</SPAN>
This results in the total sum of the prior month being repeated in each row (see table note (2)). I tried tweaking my original formula (PrevMonth2 above) that semi-worked on the workdays, but every row came back blank:</SPAN>
=sumx(values(Calendar[FormatDate]),calculate(calculate(sum(SalesData[Nets Sales]),parallelperiod(Calendar[FormatDate],-1,month), filter(all(Calendar[FormatDate]), Calendar[FormatDate]=max(Calendar[FormatDate])))))</SPAN>
So I tried, DATEADD:</SPAN>
=calculate (sum(SalesData[Nets Sales]),dateadd(Calendar[FormatDate],-30,day)) and that yielded:</SPAN>
ERROR – CALCULATION ABORTED: MdxScript(Sandbox) (22,88) Function ‘DATEADD’ only works with contiguous date selections.
Not sure why this common function was not included in the date functions (or is it?) but it can't be this hard to figure out. Please note, I am a business analyst proficient in excel and access, but my dev language skills are not up to par, so please provide a "dummy" explanation if possible.
Thanks in advance for your help, and please let me know if you need any more information!
| FEB MTD SALES | FEB SALES | JAN SALES | DEC SALES | ||
FormatDate | Workday | MTDSales | CurrentDailySales | PrevMonthDay | PrevMonth2 |
| 2/1/2012 | 0 | (2)34,252,945.98 | |||
| 2/2/2012 | 1 | 16,068.03 | 16,068.03 | 34,252,945.98 | |
| 2/3/2012 | 2 | 915,608.42 | 899,540.39 | 34,252,945.98 | |
| 2/4/2012 | 0 | 915,608.42 | 34,252,945.98 | ||
| 2/5/2012 | 0 | 915,608.42 | 0 | 34,252,945.98 | |
| 2/6/2012 | 3 | 1,769,343.20 | 853,734.78 | 34,252,945.98 | |
| 2/7/2012 | 4 | 2,869,342.91 | 1,099,999.71 | 34,252,945.98 | |
| 2/8/2012 | 5 | 3,680,255.06 | 810,912.15 | 34,252,945.98 | |
| 2/9/2012 | 6 | 4,248,900.12 | 568,645.06 | 34,252,945.98 | |
| 2/10/2012 | 7 | 4,707,046.44 | 458,146.32 | 34,252,945.98 | |
| 2/11/2012 | 0 | 4,707,091.45 | 45.01 | 34,252,945.98 | |
| 2/12/2012 | 0 | 4,708,506.55 | 1,415.10 | 34,252,945.98 | |
| 2/13/2012 | 8 | 7,575,284.92 | 2,866,778.37 | 34,252,945.98 | |
| 2/14/2012 | 9 | 8,955,928.20 | 1,380,643.28 | 34,252,945.98 | |
| 2/15/2012 | 10 | 10,405,765.29 | 1,449,837.09 | 34,252,945.98 | |
| 2/16/2012 | 11 | 11,392,932.41 | 987,167.12 | 34,252,945.98 | |
| 2/17/2012 | 12 | 12,142,417.32 | 749,484.91 | 34,252,945.98 | |
| 2/18/2012 | 0 | 12,142,417.32 | 0 | 34,252,945.98 | |
| 2/19/2012 | 0 | 12,145,939.80 | 3,522.48 | 34,252,945.98 | |
| 2/20/2012 | 13 | 12,996,084.44 | 850,144.64 | 34,252,945.98 | |
| 2/21/2012 | 14 | 15,205,507.56 | 2,209,423.12 | 34,252,945.98 | |
| 2/22/2012 | 15 | 16,419,536.29 | 1,214,028.73 | 34,252,945.98 | |
| 2/23/2012 | 16 | 17,608,026.94 | 1,188,490.65 | 34,252,945.98 | |
| 2/24/2012 | 17 | 18,624,365.30 | 1,016,338.36 | 34,252,945.98 | |
| 2/25/2012 | 0 | 18,631,741.68 | 7,376.38 | 34,252,945.98 | |
| 2/26/2012 | 0 | 18,631,741.68 | 0 | 34,252,945.98 | |
| 2/27/2012 | 18 | 20,503,919.03 | 1,872,177.35 | 34,252,945.98 | |
| 2/28/2012 | 19 | 22,955,686.41 | 2,451,767.38 | 34,252,945.98 | |
| 2/29/2012 | 20 | 30,372,849.13 | 7,417,162.72 | 34,252,945.98 | |
30,372,849.13 | 30,372,849.13 | 155,175,568.15 | |||
| 3/1/2012 | 0 | 30,372,849.13 | (1) 255,517.07 | ||
3/2/2012 | 1 | 30,372,849.13 | |||
| 3/3/2012 | 0 | 20.12 | 20.12 | 30,372,849.13 | 255,517.07 |
| 3/4/2012 | 0 | 20.12 | 30,372,849.13 | 255,517.07 | |
| 3/5/2012 | 2 | 2,963,348.85 | 2,963,328.73 | 30,372,849.13 | 992,291.80 |
| 3/6/2012 | 3 | 4,197,145.41 | 1,233,796.56 | 30,372,849.13 | 645,451.73 |
| 3/7/2012 | 4 | 5,476,891.87 | 1,279,746.46 | 30,372,849.13 | 1,114,796.73 |
| 3/8/2012 | 5 | 6,742,932.88 | 1,266,041.01 | 30,372,849.13 | 886,808.60 |
| 3/9/2012 | 6 | 7,874,065.63 | 1,131,132.75 | 30,372,849.13 | 758,167.99 |
| 3/10/2012 | 0 | 7,874,065.63 | 0 | 30,372,849.13 | 255,517.07 |
| 3/11/2012 | 0 | 7,874,065.63 | 30,372,849.13 | 255,517.07 | |
| 3/12/2012 | 7 | 9,223,051.35 | 1,348,985.72 | 30,372,849.13 | 1,165,440.64 |
| 3/13/2012 | 8 | 9,223,051.35 | 0 | 30,372,849.13 | 1,298,628.80 |
| 3/14/2012 | 9 | 9,231,557.71 | 8,506.36 | 30,372,849.13 | 1,644,676.96 |
| 3/15/2012 | 10 | 9,231,515.29 | -42.42 | 30,372,849.13 | 1,211,714.67 |
| 3/16/2012 | 11 | 9,232,392.51 | 877.22 | 30,372,849.13 | 749,377.47 |
| 3/17/2012 | 0 | 9,232,392.51 | 30,372,849.13 | 255,517.07 | |
| 3/18/2012 | 0 | 9,232,392.51 | 30,372,849.13 | 255,517.07 | |
| 3/19/2012 | 12 | 9,232,392.51 | 30,372,849.13 | 1,866,223.31 | |
| 3/20/2012 | 13 | 9,232,584.51 | 192 | 30,372,849.13 | 1,217,806.95 |
| 3/21/2012 | 14 | 9,251,390.81 | 18,806.30 | 30,372,849.13 | 2,025,403.93 |
| 3/22/2012 | 15 | 9,251,390.81 | 30,372,849.13 | 2,368,277.10 | |
| 3/23/2012 | 16 | 9,308,183.21 | 56,792.40 | 30,372,849.13 | 1,637,338.32 |
| 3/24/2012 | 0 | 9,308,183.21 | 0 | 30,372,849.13 | 255,517.07 |
| 3/25/2012 | 0 | 9,308,183.21 | 30,372,849.13 | 255,517.07 | |
| 3/26/2012 | 17 | 9,308,183.21 | 30,372,849.13 | 1,432,966.43 | |
| 3/27/2012 | 18 | 9,376,745.78 | 68,562.57 | 30,372,849.13 | 1,559,190.98 |
| 3/28/2012 | 19 | 9,439,500.83 | 62,755.05 | 30,372,849.13 | 2,190,218.67 |
| 3/29/2012 | 20 | 9,609,957.70 | 170,456.87 | 30,372,849.13 | 9,232,647.83 |
| 3/30/2012 | 21 | 9,609,957.70 | 30,372,849.13 | ||
| 3/31/2012 | 0 | 9,609,965.50 | 7.8 | 30,372,849.13 | 255,517.07 |
9,609,965.50 | 9,609,965.50 | 185,548,417.28 | 34,252,945.98 |
<TBODY>
</TBODY>
