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

     

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

 
  • tmears's avatar
    tmears
    Helper III
    6 years ago

    • jdbuchanan71's avatar
      jdbuchanan71
      Super User
      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 )
    )
)

       

      • tmears's avatar
        tmears
        Helper III
        6 years ago

        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.