Forum Discussion
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!
1 Reply
- v-yulgu-msftMicrosoft Employee
Hi Reigning_data,
You should add [dates] from calendar table into chart X-axis. Besides, please use SAMEPERIODLASTYEAR to calculate MAT in previous year.
Regards,
Yuliana Gu