Nested DAX Query

Question

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

Input would be like:

month   inventory
1        5
2        9
3        7
4        11

Desired output is:

reportMonth average3MthInventory
3           7
4           9

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.

So far, the query uses summarize to produce an intermediate table of monthly inventory numbers i.e.

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

This produces an intermediate table:

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

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

At this point, I wrapped the query above in another summarize to compute the final numbers:

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

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

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

Any ideas appreciated.

Edit in response to Alejandro Zuleta:

Alejandro,

Thanks for the quick response. Further info follows ...

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.

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

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 <= 3 and offMarket month is > 3 (or blank). Based on the table above, inventory would be as follows:

inventory

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

...

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 :

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

This works. The hangup is ... how to extend this to create a moving average inventory.

Update Figured it out. The key was switching from summarize to groupBy(https://msdn.microsoft.com/en-us/library/mt163693.aspx).

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.

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])  
)

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-11-09T14:37:02

You can create a measure to get the desired average by using EARLIER function.

average3MthInventory =
IF (
    MAX ( [month] ) > 2,
    CALCULATE (
        AVERAGE ( Inventory[inventory] ),
        FILTER (
            ALL ( Inventory[month] ),
            COUNTROWS (
                FILTER (
                    Inventory,
                    EARLIER ( [month] )
                        >= [month] - 2
                        && [month] >= EARLIER ( Inventory[month] )
                )
            )
        )
    ),
    BLANK ()
)

Then just use that measure in your pivot table or Power BI visualization.

However if you need to get a table you can use this expression:

EVALUATE
FILTER (
    SUMMARIZE (
        ADDCOLUMNS (
            ADDCOLUMNS (
                Inventory,
                "MinMonth", IF ( Inventory[month] > 2, Inventory[month] - 2, BLANK () )
            ),
            "average3MthInventory", IF (
                ISBLANK ( [MinMonth] ),
                BLANK (),
                CALCULATE (
                    AVERAGE ( Inventory[inventory] ),
                    FILTER (
                        Inventory,
                        [month] >= EARLIER ( [MinMonth] )
                            && [month] <= EARLIER ( [month] )
                    )
                )
            )
        ),
        [month],
        [average3MthInventory]
    ),
    NOT ISBLANK ( [average3MthInventory] )
)

Assuming your input table is called Inventory you should get this:

UPDATE:

The inventory table produced by your DAX expression cannot be processed to calculate the average of the last three months. Fortunately you are using SSAS 2016 which supports calculated tables.

Calculated tables are fully operable tables produced by a DAX expression against one or more tables in your model.

You have to create a calculated table using the DAX expression that produces the inventory per month. I recommend you to name it Inventory to match the DAX query I posted above to calculate the required average.

Check these resources about calculated tables creation:

RESOURCE 1
RESOURCE 2

Once the calculated table is created use my query and you will see the average of the last three months.

I've tested this approach using calculated columns and it works.

Let me know if this helps for you.

Nested DAX Query

1 Answers