Forum Discussion

tmears's avatar
tmears
Helper III
6 years ago
Solved

MAT

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
    )
)
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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 )
    )
)

     

7 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    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's avatar
      tmears
      Helper III
      6 years ago

      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??