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

     

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. 

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        6 years ago

        tmears 

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