# Actual vs Forecast in Aggregated sums



## zakkair (Mar 6, 2013)

Hi,

I have an Excel file that currently compares the Actual data coming in vs the Forecast.

The forecast is given in months (example: Volume in Calgary is 200,000L for the month of January).

The actual data coming in is in days (example: volume for calgary on January 1st is 4,500L)

I want to make an aggregated line chart that compares the two. So for forecast, it starts a 0L and goes up by the daily volume, while the forecast is a fixed straight line going up. How would I do this in DAX?

Thanks in advance for the help. I greatly appreciate it 

Sample doc:


Actual Acquired

Product 2</SPAN>
Contract</SPAN>
Region</SPAN>
Business Group</SPAN>
Supply Orbit</SPAN>
Supply Region</SPAN>
Supplier</SPAN>
Product</SPAN>
Brand</SPAN>
Terminal</SPAN>
Load Date</SPAN>
Volume</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/9/2013</SPAN>
7139</SPAN>
ULSD #1</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/10/2013</SPAN>
2016</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/10/2013</SPAN>
3011</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/11/2013</SPAN>
4524</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/11/2013</SPAN>
5736</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/11/2013</SPAN>
6027</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/11/2013</SPAN>
6027</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/11/2013</SPAN>
6032</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/17/2013</SPAN>
7036</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/17/2013</SPAN>
10576</SPAN>
ULSD #1</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/18/2013</SPAN>
1414</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/18/2013</SPAN>
7034</SPAN>
Premium gas</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
PRM</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/21/2013</SPAN>
1010</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/21/2013</SPAN>
3523</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/22/2013</SPAN>
2215</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/22/2013</SPAN>
11072</SPAN>
ULSD</SPAN>
Joe - Gas</SPAN>
West</SPAN>
WHOLESALE</SPAN>
Burnaby</SPAN>
Pacific</SPAN>
Joe</SPAN>
DIS</SPAN>
Unbranded</SPAN>
10227</SPAN>
1/22/2013</SPAN>
3827</SPAN>


<TBODY>

</TBODY>
Forecast

Supplier</SPAN>Contract</SPAN>Region2</SPAN>Orbit</SPAN>Brand</SPAN>Lift Point</SPAN>Product Class</SPAN>Product</SPAN>Product Detail</SPAN> Jan 2013 </SPAN> Feb 2013 </SPAN> Mar 2013 </SPAN> Apr 2013 </SPAN> May 2013 </SPAN> Jun 2013 </SPAN> Jul 2013 </SPAN> Aug 2013 </SPAN> Sep 2013 </SPAN> Oct 2013 </SPAN> Nov 2013 </SPAN> Dec 2013 </SPAN>Total</SPAN>Joe</SPAN>Joe - Gas</SPAN>West</SPAN>Pacific</SPAN>Unbranded</SPAN>Burnaby</SPAN>Gas</SPAN>Premium gas</SPAN>Premium gas</SPAN>      200,000 </SPAN>      300,000 </SPAN>      300,000 </SPAN>   300,000 </SPAN>   400,000 </SPAN>    400,000 </SPAN>   400,000 </SPAN>   400,000 </SPAN>   400,000 </SPAN>   300,000 </SPAN>   300,000 </SPAN>   300,000 </SPAN>########</SPAN>Joe</SPAN>Joe - Gas</SPAN>West</SPAN>Pacific</SPAN>Unbranded</SPAN>Burnaby</SPAN>Gas</SPAN>E10</SPAN>E10</SPAN>   2,200,000 </SPAN>   1,700,000 </SPAN>   1,700,000 </SPAN>########</SPAN>########</SPAN> 1,700,000 </SPAN>########</SPAN>########</SPAN>########</SPAN>########</SPAN>########</SPAN>########</SPAN>########</SPAN>Joe</SPAN>Joe - Gas</SPAN>West</SPAN>Pacific</SPAN>Unbranded</SPAN>Burnaby</SPAN>Distillate</SPAN>ULSD</SPAN>ULSD</SPAN>   2,000,000 </SPAN>   2,000,000 </SPAN>   2,000,000 </SPAN>########</SPAN>########</SPAN> 2,000,000 </SPAN>########</SPAN>########</SPAN>########</SPAN>########</SPAN>########</SPAN>########</SPAN>########</SPAN>Joe</SPAN>Joe - Gas</SPAN>West</SPAN>Pacific</SPAN>Unbranded</SPAN>Burnaby</SPAN>Distillate</SPAN>ULSD</SPAN>ULSD #1</SPAN>      200,000 </SPAN>      150,000 </SPAN>150000</SPAN>100000</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>0</SPAN>150000</SPAN>200000</SPAN>125000</SPAN>1075000</SPAN>

<TBODY>

</TBODY><COLGROUP><COL><COL><COL><COL><COL span=4><COL span=4><COL span=2><COL><COL span=7></COLGROUP>


----------



## powerpivotpro (Mar 6, 2013)

Have you seen these posts?

Sales/Budget: Integrating Data of Different “Grains” « PowerPivotPro 
Data of Different Grains: A Followup « PowerPivotPro


----------



## Jacob Barnett (Mar 6, 2013)

There's also this incredible technique from Marco Russo:

Budget and Other Data at Different Granularities in PowerPivot | SQLBI

The beauty of this approach is that you don't need relationships or additional tables to make it work AND it's a pattern that you can reuse continually once you get your head round the idea (which isn't totally straightforward).


----------



## zakkair (Mar 7, 2013)

I have already created a Actual vs Forecast in that way but it is the total comparison of each month. I want to make an aggregated chart to compare daily volume.

Right now, I'm looking to split the forecast into individual days. Example: Jan 2013 forecast is 300,000, so I want to split that off into 
Jan 1: 300000/31*1, Jan 2: 300000/31*2, Jan 3: 300,000/31*3.... to make an aggregate line chart for forecast.

This way, I can compare it to my daily actuals, Jan 1, we got 280,000, Jan 2 would be 280000(jan 1 data) + Jan 2 data.

Is this possible using formulas on DAX based on the format of my 2 tables or do I have to manually create the tables. I want to be able to filter it with slicers so I can get daily comparisions for different regions.


----------



## zakkair (Mar 7, 2013)

I have created a totalMTD for lift actuals --> totalmtd(sum('Lift Actuals'[Volume]), 'Lift Actuals'[Load Date])

I also created a measure that divide my budget january by 31. How do I make Measure 2 also aggreggate?
Similar to this:

Row Labels
Sum of Volume
MTD Sum Lift Actual
Measure 2
1/1/2013</SPAN>
5,115,814</SPAN>
5,115,814 </SPAN>
11.70967742</SPAN>
1/2/2013</SPAN>
13,207,893</SPAN>
18,323,706 </SPAN>
11.70967742</SPAN>
1/3/2013</SPAN>
11,507,828</SPAN>
29,831,534 </SPAN>
11.70967742</SPAN>
1/4/2013</SPAN>
11,787,756</SPAN>
41,619,290 </SPAN>
11.70967742</SPAN>
1/5/2013</SPAN>
9,661,009</SPAN>
51,280,299 </SPAN>
11.70967742</SPAN>


<TBODY>

</TBODY>


----------



## marco.russo (Mar 8, 2013)

Maybe you want to write this:
	
	
	
	
	
	



```
M2YTD := SUMX( DATESYTD( 'Lift Actuals'[Load Date] ), [Measure 2] )
```


----------



## zakkair (Mar 8, 2013)

Hi Marco, it works really good for the annual data. Thanks a lot :D

How would I fix the formula for it to be split off into months. For example, right now when I filter the month-year to only show February 2013 data, the M2YTD starts off at the total of January 2013. How would I change the measure to make it start off at February 2013. My MTD Sum Lift Actual starts over from the beginning of Feb when I filter it. I was hoping the M2YTD would too.

Nevermind! I went around it and did this:   
sumx(Datesytd('Lift Actuals'[Load Date]), [BU Forecast Daily Feb])-31*[bu forecast daily feb] 

for another pivot table underneath and filtered it to February month only so I can compare the monthly data. It seems to work, just need to write a new measure for every month.

Thanks a lot! If there's another way to make it easier, I'll be so thankful!


----------



## marco.russo (Mar 8, 2013)

If you want a running total (which is not a YTD, but a running total of visible data in the PivotTable), use this: 
	
	
	
	
	
	



```
M2YTD := SUMX( DATESYTD( ALLSELECTED( 'Lift Actuals'[Load Date] ) ), [Measure 2] )
```


----------

