cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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
)
)
1 ACCEPTED SOLUTION
Super User

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 7
Super User

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

Helper III

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

Helper III

Super User

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

Helper III

@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.

Super User

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

Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors