Forum Discussion
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...
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 ) ) )
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] )
)
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??
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 ) ) )
