Forum Discussion

Reigning_data's avatar
Reigning_data
Frequent Visitor
7 years ago

Comparing Current and Previous Moving Annual Total (MAT)

Hello All,

 

Context: I am trying to compare the Current MAT with Previous MAT by month. The idea being that I would have a bar, or line, graph, showing 'current MAT' as of Aug 2018 compared to 'Previous MAT' as of Aug 2017.

 

So Far: I've been fiddling with a couple of different MAT formulae.

 

MAT Current Year = 
CALCULATE (
    [Total Value],
    FILTER (
        ALL ( 'Calendar'),
        'Calendar'[Sequential Day Number] > MAX ( 'Calendar'[Sequential Day Number] ) - 365
             && 'Calendar'[Sequential Day Number] <= MAX ( 'Calendar'[Sequential Day Number] )
    )
)

And.

MAT Current Year V2 = 
CALCULATE (
    SUMX(
        FILTER( 'table1','table1'[sales]),
        [Total Value]
    ),
    DATESINPERIOD(
        'Calendar'[Date],LASTDATE ( 'Calendar'[Date]),
        -12,
        MONTH
    )
)

These are doing exactly what they are supposed to - calculate the MAT each month.

 

Problem: So, how do I go about displaying a 'previous MAT' side-by-side with the current? At first I tried the date criteria to be 'a year earlier' like so:

 

MAT Previous Year = 
 
CALCULATE (
    [Total Value],
    FILTER (
        ALL ( 'Calendar'),
        'Calendar'[Sequential Day Number] > MAX( 'Calendar'[Sequential Day Number] ) - 730
             && 'Calendar'[Sequential Day Number] <= MAX ( 'Calendar'[Sequential Day Number] ) - 365 )
)

But it does not come out with the desired results:

 

By YearBy Month

 

Is this an issue with formula or an issue with how I am displaying it?

 

Many thanks in advance!