cancel
Showing results for
Did you mean:
Highlighted
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?

Thanks

1 ACCEPTED SOLUTION

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

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

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 ?

Regular Visitor

## Re: Measure to sum data of latest month

 Sales Location Month Number Month 1 Site A 1 January 1 Site A 2 February 2 Site A 3 March 3 Site A 4 April 3 Site A 5 May 3 Site A 6 June 4 Site A 7 July 1 Site B 1 January 1 Site B 2 February 2 Site B 3 March 3 Site B 4 April 3 Site B 5 May 3 Site B 6 June 4 Site B 7 July 1 Site C 1 January 1 Site C 2 February 2 Site C 3 March 3 Site C 4 April 3 Site C 5 May 3 Site C 6 June 4 Site C 7 July
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.

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?

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

Regular Visitor

## Re: Measure to sum data of latest month

Brilliant thank you for your help!

Announcements

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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 260 members 2,916 guests
Recent signins: