Skip to main content
cancel
Showing results for 
Search instead 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

Reply
tmears
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
jdbuchanan71
Super User
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] ) 
)

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

 

@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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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