I need to create a measure that calculates the sum of total sales from the latest month only for each site. See image below.
I should get a total of 12, as it should SUM 4,4,4 from July, for each of Site a, b and c.
How can I do this?
Go to Solution.
Here you go,
VAR MAxxx = MAX(Table1[Month Number ])
CALCULATE(SUM(Table1[Sales]), Table1[Month Number ] = MAxxx)
This will work based on months.if you add other years, the same months of other years will also be taken into account! so watch out.
What does your "Date" value look like in the original data? is it text or a date value?
What do you want your output to look like? just a card?
The date value is just a text value of the month name only.
Yes just a card figure would be fine thanks
Could you be so kind to copy that table in here? Instead of a screenshot ?
Please see below!
Thanks very much that does work! What do we do in the event when other years are added on, do we just add a filter?
VAR MAxM = MAX(Table1[Month Number ])VAR MAxY = MAX(Table1[Year Number ])
CALCULATE(SUM(Table1[Sales]), Table1[Month Number ] = MAxM; Table1[Year Number ] = MAxY )
This is a good workaround, but not an ideal way to filter your data. Ideally you would work with a data value.But this should work
Brilliant thank you for your help!
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.