cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vickygibbons123 Regular Visitor
Regular Visitor

Measure to sum data of latest month

Hi there,

 

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?

 

ThanksPowerBIImageCommunity.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
RobbeVL Established Member
Established Member

Re: Measure to sum data of latest month

Here you go,

meaasure = 
VAR MAxxx = MAX(Table1[Month Number ])
RETURN
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.

 

 

8 REPLIES 8
RobbeVL Established Member
Established Member

Re: Measure to sum data of latest month

Hi there,

 

What does your "Date" value look like in the original data? is it text or a date value?

Calculate(Sum(Sales); LastDate(Month))

What do you want your output to look like? just a card?

 

vickygibbons123 Regular Visitor
Regular Visitor

Re: Measure to sum data of latest month

Hi, 

 

The date value is just a text value of the month name only.

 

Yes just a card figure would be fine thanks

RobbeVL Established Member
Established Member

Re: Measure to sum data of latest month

Could you be so kind to copy that table in here? Instead of a screenshot ? Smiley Happy

vickygibbons123 Regular Visitor
Regular Visitor

Re: Measure to sum data of latest month

Please see below!

 

SalesLocationMonth Number Month
1Site A1January
1Site A2February
2Site A3March
3Site A4April
3Site A5May
3Site A6June
4Site A7July
1Site B1January
1Site B2February
2Site B3March
3Site B4April
3Site B5May
3Site B6June
4Site B7July
1Site C1January
1Site C2February
2Site C3March
3Site C4April
3Site C5May
3Site C6June
4Site C7July
RobbeVL Established Member
Established Member

Re: Measure to sum data of latest month

Here you go,

meaasure = 
VAR MAxxx = MAX(Table1[Month Number ])
RETURN
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.

 

 

vickygibbons123 Regular Visitor
Regular Visitor

Re: Measure to sum data of latest month

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?

RobbeVL Established Member
Established Member

Re: Measure to sum data of latest month

meaasure = 
VAR MAxM = MAX(Table1[Month Number ])
VAR MAxY = MAX(Table1[Year Number ]) RETURN 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 Smiley Happy 

vickygibbons123 Regular Visitor
Regular Visitor

Re: Measure to sum data of latest month

Brilliant thank you for your help!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 42 members 1,111 guests
Please welcome our newest community members: