cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
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

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

 

View solution in original post

7 REPLIES 7
Super User II
Super User II

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

 MatVolume.jpg

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

 

snapshotbi.PNG

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

 

View solution in original post

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

per buying.PNG

@tmears 

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

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

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

Top Solution Authors
Top Kudoed Authors