Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
RobbeVL
Impactful Individual
Impactful Individual

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.

 

 

View solution in original post

8 REPLIES 8
RobbeVL
Impactful Individual
Impactful Individual

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?

 

Anonymous
Not applicable

Hi, 

 

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

 

Yes just a card figure would be fine thanks

RobbeVL
Impactful Individual
Impactful Individual

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

Anonymous
Not applicable

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
Impactful Individual
Impactful Individual

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.

 

 

Anonymous
Not applicable

Brilliant thank you for your help!

Anonymous
Not applicable

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
Impactful Individual
Impactful Individual

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 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.