cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

Alternative to EARLIER in DAX measure

Hi all,

I am trying to obtain a measure that sums the total quantity of Sources that have the same Location. It has to be a measure because the result is dependant on other measures.

So here the first Source England with Location New York, has 8 other Sources with New York, so the expected result is 608+369+359+129+68 etc (notice it skipped India)

 Source Quantity Location Expected result England 608 New York 1633 New York 369 Colombia 369 New York 359 Colombia 359 New York 129 Mexico 129 India 10 USA 96 New York 68 Japan 68 New York 38 Colombia 38 New York 36 Croatia 36 New York 22 China 22 New York 4 Italy 4

Any ideas?

Before I had all this in excel and I was using:

Expected Result = [Quantity]

+ SUMX (

FILTER ( Table, EARLIER ( Location ) = Source)

[Quantity])

1 ACCEPTED SOLUTION
Super User

@PowerBITestingG try this measure:

``````Sum Qty = SUM ( Source[Quantity] )

Sum =
SUMX (
Source,
VAR __location = Source[Location]
VAR __value = CALCULATE ( [Sum Qty], ALL ( Source ), Source[Source] = __location )
RETURN
__value + [Sum Qty]
)``````

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@PowerBITestingG try this measure:

``````Sum Qty = SUM ( Source[Quantity] )

Sum =
SUMX (
Source,
VAR __location = Source[Location]
VAR __value = CALCULATE ( [Sum Qty], ALL ( Source ), Source[Source] = __location )
RETURN
__value + [Sum Qty]
)``````

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors