Nested DAX Query
<p>Am an experienced SQL guy ... new to DAX.  I'm trying to create 
a DAX query to generate a moving average inventory.<br>
The internals of the inventory calculation are a bit complicated, but
hopefully not necessary for this discussion.  </p>

<p>Input would be like: </p>

<pre><code>month   inventory
1        5
2        9
3        7
4        11
</code></pre>

<p>Desired output is:</p>

<pre><code>reportMonth average3MthInventory
3           7
4           9
</code></pre>

<p>That is, the avg inventory to be reported for month 3 is the trailing
average of months 1,2,3 = (5+9+7) / 3 = 7 ... month 4 would be the
average of values for months 2,3,4.  </p>

<p>So far, the query uses <em>summarize</em> to produce an intermediate table
of monthly inventory numbers i.e.</p>

<pre><code>evaluate
filter(
  crossJoin(
    summarize(
      ...
        , reportMonths[month]   -- group by
      , "inventory", count(widgets[widgetID]) 
    ) -- end summarize
  ) -- end crossJoin
)  -- end filter
</code></pre>

<p>This produces an intermediate table:</p>

<pre><code>reportMonths[month] actualMonths[month] [inventory] ... plus some other columns  
    3                   1                   5
    3                   2                   9
    3                   3                   7
    4                   2                   9
    4                   3                   7
    4                   4                   11
</code></pre>

<p>This is exactly as expected.  From this, should be able to average
the inventory over the report month.  </p>

<p>At this point, I wrapped the query above in another <em>summarize</em> to
compute the final numbers: </p>

<pre><code>evaluate
summarize(
  filter(
    crossJoin(
      summarize(
        ...
        , reportMonths[month]   -- group by
        , "inventory", count(widgets[widgetID]) 
      ) -- end summarize
    ) -- end crossJoin
  )  -- end filter
  , "month", reportMonths[month]
  , "avgInventory", average([inventory])
) -- summarize
</code></pre>

<p>But, this returns an error: "Cannot identify the table that contains [inventory] column."</p>

<p>Can't find a way to refer to the intermediate table.  There must be another way to structure this.  </p>

<p>Any ideas appreciated.  </p>

<hr>

<p>Edit in response to Alejandro Zuleta: </p>

<p>Alejandro, </p>

<p>Thanks for the quick response.  Further info follows ...   </p>

<p>Inventory history is built up from product "listings".  Date granularity is monthly and each listing has an onMarket month and offMarket month (months are numbered sequentially).  listings have other attributes typeID, areaID, etc.</p>

<pre><code>listingID  onMarketMonth  offMarketMonth ... other attribs - typeID, etc.
101        1              2
103        1              6
105        2              2
106        2              
109        2              3
117        3              4
123        3              
124        3              9
</code></pre>

<p>Inventory is computed based on the onMarket and offMarket months e.g. in month 3 inventory is the number of listings where the onMarket month &lt;= 3 and offMarket month is > 3 (or blank). Based on the table above, inventory would be as follows:</p>

<p>inventory</p>

<pre><code>month   inventory   note: listings in inventory
1       2           101, 103
2       3           103, 106, 109
3       5           103, 106, 117, 123, 124
4       4           103, 106, 123, 124
</code></pre>

<p>...</p>

<p>Need to be able to report a series of values e.g. for a chart as well as moving averages.  Example code for a series of months 2 through 6 for a particular type and area would be :</p>

<pre><code>evaluate
summarize(
    filter(
        crossJoin(
            calculateTable(listings, listings[typeID] = 47)
            , filter(reportMonths, [month] &gt;= 2 &amp;&amp; [month] &lt;= 6 )
        ) -- crossjoin
        , listings[onMarketMonth] &lt;= reportMonths[month] &amp;&amp; (or(listings[offMarketMonth] &gt; reportMonths[month], isBlank(listings[offMarketMonth]))) -- join condition
    ) -- filter the join
    , reportMonths[month]
    , "inventory",count(listings[listingID])
) -- summarize
</code></pre>

<p>This works.  The hangup is ... how to extend this to create a moving average inventory. </p>

<p><strong>Update</strong>
Figured it out.  The key was switching from <em>summarize</em> to <em>groupBy</em>(<a href="https://msdn.microsoft.com/en-us/library/mt163693.aspx" rel="nofollow noreferrer">https://msdn.microsoft.com/en-us/library/mt163693.aspx</a>).  </p>

<p>Notice in the sample code (below) that [Total Sales] is defined/calculated in the GroupBy in the VAR and then referenced in the query below (in the GroupBy in the Evaluate).  When I tried something similar using Summarize, got an error.  </p>

<pre><code>DEFINE  
VAR SalesByCountryAndCategory =  
GROUPBY (  
Sales,   
Geography[Country],   
Product[Category],   
“Total Sales”, SUMX( CURRENTGROUP(), Sales[Price] * Sales[Qty])  
)  

Evaluate GROUPBY (  
SalesByCountryAndCategory,   
Geography[Country],   
 “Max Sales”, MAXX( CURRENTGROUP(), [Total Sales])  
) 
</code></pre>