# DAX Help - Month Over Month Sale Comparison by Day



## MonicaV (Oct 5, 2012)

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!



*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></SPAN>


----------



## MD610 (Oct 8, 2012)

The first tip I can give if you want to work with the date functions in PowerPivot is to add a separate "date table" to your data model and then relate it to your data by the date column.  A date table is a just a table that lists all consecutive dates and then columns with various descriptions of those dates such as Year, Month, DayOfWeek, Quarter, etc....

A date table allows the date/time functions to work properly.  All your measures, pivots, and charts should be using dates from the date table. Search "PowerPivot Date Table" and you should find a lot of info.


----------



## MonicaV (Oct 8, 2012)

Thanks for your reply.

Sorry for not making it clearer that the table in my question is a powerpivot report resulting from several tables. [FormatDate] and [Workday] are in a separate date table (Calendar). The Calendar table is related to my SalesData table via the [FormatDate] field. Any insight on the PrevMonthDay calculation?


----------



## Mavericks334 (Oct 12, 2012)

Hi Monica,

Try this link.

Profit & Loss (Part 3)–Return On Sales and Variances « PowerPivotPro


----------



## MonicaV (Oct 12, 2012)

Thanks!  I will look at it next week and let you know how it goes.


----------

