MAT

[tmears]

Hi all

I want to calcuate the last 12 month Moving Annual totals, which i am using the following which is working.  What i am struggling with is a measure or comparrision against the same period last year.  ie 2019 against 2018.  Do i do two measures??  i am having a brain blank!!!!  Ideally having a MAT figure per month for 2018, 2019 etc etc

MAT Volume 12 =

    var DateEnd = CALCULATE(MAX('VRS'[VRS Date]))

    var DateStart = MINX(DATEADD('VRS'[VRS Date],-12 + 1,MONTH),'VRS'[VRS Date])

    var theDatesBetween = DATESBETWEEN('VRS'[VRS Date], DateStart,DateEnd)

    return

IF(NOT(ISBLANK(CALCULATE(SUM(VRS[NetSales])))),

    CALCULATE(

        SUM('VRS'[NetSales])

        ,theDatesBetween

    )

)

[jdbuchanan71]

Hello @tmears 

First, I would write a measure just for Net Sales so you have it to use in other measures.

Net Sales = SUM ( VRS[NetSales] )

Then the rolling 12 can be simplified using DATESINPERIOD

Mat Volumn 12 = 
VAR _DateEnd =
    LASTDATE ( VRS[VRS Date] )
RETURN
    CALCULATE (
        [Net Sales],
        ALL ( VRS ),
        DATESINPERIOD ( VRS[VRS Date], _DateEnd, -12, MONTH )
    )

And the prior year can use SAMEPERIODLASTYEAR

Mat Volumn PY = 
CALCULATE ( 
    [Mat Volumn 12], 
    ALL ( VRS ), 
    SAMEPERIODLASTYEAR ( VRS[VRS Date] ) 
)

 

[tmears]

Perfect thanks @jdbuchanan71 the only thing i have as a problem now is that the measaure is pulling in a figure for the next couple of month ie nov and dec, (the data is unto end of Oct) can i remove these??

[tmears]

[jdbuchanan71 · Accepted solution]

For that one you can put in the IF ( ISBLANK () from your original:

Mat Volumn 12 = 
VAR _DateEnd =
    LASTDATE ( VRS[VRS Date] )
RETURN
IF ( ISBLANK ( [Net Sales] ), BLANK(),
    CALCULATE (
        [Net Sales],
        ALL ( VRS ),
        DATESINPERIOD ( VRS[VRS Date], _DateEnd, -12, MONTH )
    )
)

[tmears]

@jdbuchanan71you are a star, thank you so much!!  Not sure if i can push my luck, i also need to do this calculation based upon the customer called buying group.  but obviously if i bring in the 'buying group' it returns a total sum.  the customer called buying group.  but obviously if i bring in the 'buying group' it returns a total sum. 

[jdbuchanan71]

@tmears 

Change the ALL ( VRS ) to ALL ( VRS[VRS Date] ) which should repect the other filters. 

[tmears]

thank you so much..... you hero status in my office at the moment!!