DAX Help - Month Over Month Sale Comparison by Day

M

MonicaV

New Member
Joined
Oct 5, 2012
Messages
5
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!


[TABLE="class: grid, width: 288, align: left"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]FEB MTD SALES
[/TD]
[TD="width: 64, bgcolor: transparent"]FEB SALES
[/TD]
[TD="width: 64, bgcolor: transparent"]JAN SALES
[/TD]
[TD="width: 64, bgcolor: transparent"]DEC SALES
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
FormatDate​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Workday​
[/TD]
[TD="width: 64, bgcolor: transparent"]
MTDSales​
[/TD]
[TD="width: 64, bgcolor: transparent"]
CurrentDailySales​
[/TD]
[TD="width: 64, bgcolor: transparent"]
PrevMonthDay​
[/TD]
[TD="width: 64, bgcolor: transparent"]
PrevMonth2​
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/1/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
(2)34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/2/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1
[/TD]
[TD="width: 64, bgcolor: transparent"]
16,068.03​
[/TD]
[TD="width: 64, bgcolor: transparent"]
16,068.03​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/3/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]2
[/TD]
[TD="width: 64, bgcolor: transparent"]
915,608.42​
[/TD]
[TD="width: 64, bgcolor: transparent"]
899,540.39​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/4/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
915,608.42​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/5/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
915,608.42​
[/TD]
[TD="width: 64, bgcolor: transparent"]
0​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/6/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,769,343.20​
[/TD]
[TD="width: 64, bgcolor: transparent"]
853,734.78​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/7/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]4
[/TD]
[TD="width: 64, bgcolor: transparent"]
2,869,342.91​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,099,999.71​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/8/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5
[/TD]
[TD="width: 64, bgcolor: transparent"]
3,680,255.06​
[/TD]
[TD="width: 64, bgcolor: transparent"]
810,912.15​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/9/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]6
[/TD]
[TD="width: 64, bgcolor: transparent"]
4,248,900.12​
[/TD]
[TD="width: 64, bgcolor: transparent"]
568,645.06​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/10/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]7
[/TD]
[TD="width: 64, bgcolor: transparent"]
4,707,046.44​
[/TD]
[TD="width: 64, bgcolor: transparent"]
458,146.32​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/11/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
4,707,091.45​
[/TD]
[TD="width: 64, bgcolor: transparent"]
45.01​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/12/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
4,708,506.55​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,415.10​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/13/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]8
[/TD]
[TD="width: 64, bgcolor: transparent"]
7,575,284.92​
[/TD]
[TD="width: 64, bgcolor: transparent"]
2,866,778.37​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/14/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]9
[/TD]
[TD="width: 64, bgcolor: transparent"]
8,955,928.20​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,380,643.28​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/15/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]10
[/TD]
[TD="width: 64, bgcolor: transparent"]
10,405,765.29​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,449,837.09​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/16/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]11
[/TD]
[TD="width: 64, bgcolor: transparent"]
11,392,932.41​
[/TD]
[TD="width: 64, bgcolor: transparent"]
987,167.12​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/17/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12
[/TD]
[TD="width: 64, bgcolor: transparent"]
12,142,417.32​
[/TD]
[TD="width: 64, bgcolor: transparent"]
749,484.91​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/18/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
12,142,417.32​
[/TD]
[TD="width: 64, bgcolor: transparent"]
0​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/19/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
12,145,939.80​
[/TD]
[TD="width: 64, bgcolor: transparent"]
3,522.48​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/20/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13
[/TD]
[TD="width: 64, bgcolor: transparent"]
12,996,084.44​
[/TD]
[TD="width: 64, bgcolor: transparent"]
850,144.64​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/21/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]14
[/TD]
[TD="width: 64, bgcolor: transparent"]
15,205,507.56​
[/TD]
[TD="width: 64, bgcolor: transparent"]
2,209,423.12​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/22/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]15
[/TD]
[TD="width: 64, bgcolor: transparent"]
16,419,536.29​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,214,028.73​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/23/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]16
[/TD]
[TD="width: 64, bgcolor: transparent"]
17,608,026.94​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,188,490.65​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/24/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]17
[/TD]
[TD="width: 64, bgcolor: transparent"]
18,624,365.30​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,016,338.36​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/25/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
18,631,741.68​
[/TD]
[TD="width: 64, bgcolor: transparent"]
7,376.38​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/26/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
18,631,741.68​
[/TD]
[TD="width: 64, bgcolor: transparent"]
0​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/27/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]18
[/TD]
[TD="width: 64, bgcolor: transparent"]
20,503,919.03​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,872,177.35​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/28/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]19
[/TD]
[TD="width: 64, bgcolor: transparent"]
22,955,686.41​
[/TD]
[TD="width: 64, bgcolor: transparent"]
2,451,767.38​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2/29/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]20
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
7,417,162.72​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
155,175,568.15​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/1/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
(1) 255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
3/2/2012​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/3/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
20.12​
[/TD]
[TD="width: 64, bgcolor: transparent"]
20.12​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/4/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
20.12​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/5/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]2
[/TD]
[TD="width: 64, bgcolor: transparent"]
2,963,348.85​
[/TD]
[TD="width: 64, bgcolor: transparent"]
2,963,328.73​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
992,291.80​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/6/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3
[/TD]
[TD="width: 64, bgcolor: transparent"]
4,197,145.41​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,233,796.56​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
645,451.73​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/7/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]4
[/TD]
[TD="width: 64, bgcolor: transparent"]
5,476,891.87​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,279,746.46​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,114,796.73​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/8/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5
[/TD]
[TD="width: 64, bgcolor: transparent"]
6,742,932.88​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,266,041.01​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
886,808.60​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/9/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]6
[/TD]
[TD="width: 64, bgcolor: transparent"]
7,874,065.63​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,131,132.75​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
758,167.99​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/10/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
7,874,065.63​
[/TD]
[TD="width: 64, bgcolor: transparent"]
0​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/11/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
7,874,065.63​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/12/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]7
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,223,051.35​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,348,985.72​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,165,440.64​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/13/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]8
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,223,051.35​
[/TD]
[TD="width: 64, bgcolor: transparent"]
0​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,298,628.80​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/14/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]9
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,231,557.71​
[/TD]
[TD="width: 64, bgcolor: transparent"]
8,506.36​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,644,676.96​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/15/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]10
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,231,515.29​
[/TD]
[TD="width: 64, bgcolor: transparent"]
-42.42​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,211,714.67​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/16/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]11
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,232,392.51​
[/TD]
[TD="width: 64, bgcolor: transparent"]
877.22​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
749,377.47​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/17/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,232,392.51​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/18/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,232,392.51​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/19/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]12
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,232,392.51​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,866,223.31​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/20/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]13
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,232,584.51​
[/TD]
[TD="width: 64, bgcolor: transparent"]
192​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,217,806.95​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/21/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]14
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,251,390.81​
[/TD]
[TD="width: 64, bgcolor: transparent"]
18,806.30​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
2,025,403.93​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/22/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]15
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,251,390.81​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
2,368,277.10​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/23/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]16
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,308,183.21​
[/TD]
[TD="width: 64, bgcolor: transparent"]
56,792.40​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,637,338.32​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/24/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,308,183.21​
[/TD]
[TD="width: 64, bgcolor: transparent"]
0​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/25/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,308,183.21​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/26/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]17
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,308,183.21​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,432,966.43​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/27/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]18
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,376,745.78​
[/TD]
[TD="width: 64, bgcolor: transparent"]
68,562.57​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
1,559,190.98​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/28/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]19
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,439,500.83​
[/TD]
[TD="width: 64, bgcolor: transparent"]
62,755.05​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
2,190,218.67​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/29/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]20
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,609,957.70​
[/TD]
[TD="width: 64, bgcolor: transparent"]
170,456.87​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,232,647.83​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/30/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]21
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,609,957.70​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]3/31/2012
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,609,965.50​
[/TD]
[TD="width: 64, bgcolor: transparent"]
7.8​
[/TD]
[TD="width: 64, bgcolor: transparent"]
30,372,849.13​
[/TD]
[TD="width: 64, bgcolor: transparent"]
255,517.07​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"]
9,609,965.50​
[/TD]
[TD="width: 64, bgcolor: transparent"]
9,609,965.50​
[/TD]
[TD="width: 64, bgcolor: transparent"]
185,548,417.28​
[/TD]
[TD="width: 64, bgcolor: transparent"]
34,252,945.98​
[/TD]
[/TR]
</TBODY>[/TABLE]
</SPAN>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
MAD not calculating correctly when using a 6 month rolling average
Replies
0
Views
421
peterr2006
P
G
  • Question Question
DAX formula help, WoW percent change
Replies
4
Views
588
Matt Allington
M
I
  • Question Question
Previous month sales
Replies
0
Views
465
illusionek
I
P
  • Question Question
DAX PIVOT: Projects Closure Date Field - Number of Days Change - between differing Forecast Versions
Replies
0
Views
391
phil134
P
S
  • Question Question
PowerBI Measure to calculate Sales Goals with Reports To: structure.
Replies
1
Views
266
Matt Allington
M

Forum statistics

Threads
1,223,740
Messages
6,174,223
Members
452,552
Latest member
Kleets

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top