cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors