The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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 )
)
)
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 )
)
)
@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.
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!!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the July 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
108 | |
69 | |
46 | |
43 |
User | Count |
---|---|
188 | |
87 | |
77 | |
74 | |
60 |