How to find MAT(Moving Annual Total) on ID/month level

[alfredas81]

Hello everybody,

I have one problem regarding agregation on annual basis by customer ID and month.I need to have annual sales amount yeach month and I use summarize function to get needed table

mat_monthly_cust_sales=SUMMARIZE(sales;sales[year_month];sales[user_id];"MAT";CALCULATE(SUM(sales[amount]);
DATESINPERIOD(sales[date]; LASTDATE(sales[date]); -1;YEAR
)
))

any help would be appreciated.

[v-shex-msft]

HI @alfredas81,

Please try to use below formula if it suitable for your requirement:

mat_monthly_cust_sales =
SUMMARIZE (
    sales;
    sales[year_month];
    sales[user_id];
    "MAT"; CALCULATE (
        SUM ( sales[amount] );
        FILTER (
            ALL ( sales );
            [date]
                >= DATE ( YEAR ( sales[date] ) - 1; MONTH ( sales[date] ); DAY ( sales[date] ) )
                && [date] <= MAX ( sales[date] )
        );
        VALUES ( sales[year_month] );
        VALUES ( sales[user_id] )
    )
)

If not help, please share some sample data to help test and coding formula.

Regards,

XIaoxin Sheng

[alfredas81]

Thanks v-shex-msft,

but your DAX does the same trick as mine, just it takes much more time to calculate because of values() filters(it calcucalates corectly the amount on curent month but does not take into account sales in previous months).

Basicaly my sales data is of shape:

user_id|date|calc_column(year_month)|amount

x|2017-01-11|201701|5

x|2017-02-21|201702|10

x|2018-03-07|201803|20

x|2018-04-11|201804|10

x|2018-05-21|201805|15

y|2017-01-11|201701|5

y|2017-02-21|201702|10

y|2018-03-07|201803|20

y|2018-04-11|201804|10

y|2018-05-21|201805|15

I would like to get table like this for user x(same for all the rest users in one table)

user_id|year_month|MAT

x|201701|5

x|201702|15

x|201703|15

x|201704|15

x|201705|15

x|201706|15

x|201707|15

x|201708|15

x|201709|15

x|201710|15

x|201711|15

x|201712|15

x|201803|20

x|201804|30

x|201805|45

[v-shex-msft]

Hi @alfredas81,

I modify dax formula based your sample data, please take a look at following formulas.

Table formula:

Expand Table = 
VAR _calendar =
    CROSSJOIN (
        VALUES ( '111'[user_id] ),
        SELECTCOLUMNS (
            CROSSJOIN ( VALUES ( '111'[date].[Year] ), GENERATESERIES ( 1, 12 ) ),
            "year_month", [date].[Year] * 100
                + [Value]
        )
    )
VAR original =
    SELECTCOLUMNS (
        '111',
        "user_id", [user_id],
        "year_month", [calc_column(year_month)],
        "amount", [amount]
    )
VAR remain =
    ADDCOLUMNS (
        EXCEPT (
            _calendar,
            SELECTCOLUMNS ( original, "user_id", [user_id], "year_month", [year_month] )
        ),
        "amount", 0
    )
RETURN
    UNION ( remain, original )

Calculate column rolling total:

rolling amount = 
CALCULATE (
    SUM ( 'Expand Table'[amount] ),
    FILTER (
        ALL ( 'Expand Table' ),
        [user_id] = EARLIER ( [user_id] )
            && [year_month] <= EARLIER ( [year_month] )
            && LEFT ( [year_month], 4 ) = LEFT ( EARLIER ( 'Expand Table'[year_month] ), 4 )
    )
)

Result:

Regards,

Xiaoxin Sheng

[alfredas81 · Accepted solution]

Thanks v-shex-msft,

actually I do not understand what does this part

"year_month", [date].[Year] * 100
+ [Value]

but I came to more elegant solution

ADDCOLUMNS(
CROSSJOIN(VALUES(sales[user_id]);VALUES('date table'[year_month]));
"MAT";
CALCULATE(
SUM(sales[amount]);
CALCULATETABLE(online;ALL('date table');DATESINPERIOD('date table'[Date];LASTDATE('date table'[Date]);-1;YEAR) )
)
)