DAX Rolling Averages Totals on table doesnt show

On this SQLBI video rolling average doesnt calculate anything to totals row? Why is that? Would like to have snapshot value also for rolling value on card etc

https://www.youtube.com/watch?v=ACvYaXnpyCM&t=214s

Rolling 6M Sales Amount = 
VAR numOfMonths = 6
VAR lastSelectedDate = MAX(Dim_DateTable[dte])
VAR period = DATESINPERIOD(Dim_DateTable[dte], lastSelectedDate,-numOfMonths,MONTH)

VAR result =
    CALCULATE(
        AVERAGEX(
            VALUES(Dim_DateTable[Month Of Year]),
            [Sales Amount]
        ),
        period
    )

VAR lastSalesDate = MAX(Fact_SalesInvoice[Value Date])
VAR firstVisibleDate = MAX(Dim_DateTable[dte])

RETURN
    IF(
        firstVisibleDate <= lastSalesDate, 
        result
    )

I tried also this

Rolling 6M Sales Amount B = 
VAR numOfMonths = 6
VAR rollingSum =
    CALCULATE(
        [Sales Amount],
        DATESINPERIOD(Dim_DateTable[dte], MAX(Dim_DateTable[dte]), -numOfMonths, MONTH)
    )

RETURN
    rollingSum/numOfMonths

So I'd like to understand why these dont give anything to "Totals" row but then forexample simple SUM-function gives value to totals row. Otherwise I do understand these both measures...

Solution

This code works for me, I'll try explain what is happening:

Evalulation Context

First, each cell in the visualization is a combination of row and column filters, so that expression is evaluated for a particular month, and particular calendar year. Except for the total, which does not have a single value for either of those.

We need to tell DAX how to evaluate the total. Often you can get by with using HASONEVALUE=FALSE() to identify the total column in an IF statement. In this case because it has a hierarchy I used ISINSCOPE() which tells PowerBI to calculate the result in the original problem where Month or Calendar year are in evaluation scope.

Without specifying both, it would not calculate intermediate subtotals.

Effective Date Range

Next, we need to tell PowerBI what to do in the case that neither of those are in-scope meaning it is on the grand total line. There are probably more elegant ways to handle this model but I set up a parallel calculation for last 12 months based on the OrderDate field in the Sales table. That field is keyed to the Date field.

Without doing that the result ends up being blank. I believe that has to do with there being more dates in the date table than there are matching date records in the sales table. Sales table goes to Dec 2009, but Date table extends to 2011. That will result in a bunch of blank dates in the global period -12 months that have no match in the Sales Table, and a blank result.

Normally I handle these kind of missing key issues by cutting off my date table to the current date (or prior day) in PowerQuery so I don't have to worry about dates returned that are out of range of the dates in my sales table.

In any case it seems to work if we base the period calculation for the total column off the OrderDate in the Sales Table, where there won't be missing dates to worry about.

Sales R12M = 
VAR NumOfMonths = 12
VAR LastCurrentDate =
    MAX ( 'Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )

VAR GlobalMax = CALCULATE(MAX('Sales'[Order Date]), ALL('Date'))
VAR RecentPeriod = DATESINPERIOD ( 'Sales'[Order Date], GlobalMax, - NumOfMonths, MONTH )

VAR Result = IF(OR(ISINSCOPE('Date'[Month]), ISINSCOPE('Date'[Calendar Year])),
    CALCULATE ( 
        AVERAGEX ( 
            VALUES ( 'Date'[Calendar Year Month] ), 
            [Sales Amount] 
        ),
        Period
    ),
    CALCULATE(
            AVERAGEX ( 
                VALUES ( 'Date'[Calendar Year Month] ), 
                [Sales Amount] 
            ),
            RecentPeriod

))
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] )
RETURN
Result

Sum Totals

For your last question about the sum total. Sometimes the formula engine will have no idea what to do with a complex formula outside the row/column context, and it's not always the case that a sum or average is the right answer anyway.

Where it isn't, it is better to use ISINSCOPE, HASONEFILTER, or HASONEVALUE to explicitly set out how the measure should be calculated for the total column.

But in this case, I think the issue is date table extending too far causing blanks to return when the row and column filter contexts no longer apply at the grand total level.