Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jakeudy
Helper I
Helper I

Show latest sales amount based on the created date by product and region

I am trying to create a custom column that spits out the latest sales line amount for each product by region. I tried using this code but it returned all blanks.

 

Last Sale =
var maxDate=CALCULATE(MAX('Sales'[CREATEDDATETIME].[Date]),ALLEXCEPT('Products','Products[Product Name]))
return
CALCULATE(SUM('Sales'[LINEAMOUNT]),FILTER('Sales','Sales'[CREATEDDATETIME].[Date]=maxDate))
 
There is a relationship from the Sales to the Products table using the product ID#. Here is an example of how my data is set up and my intended result: (note: Product Name and Region both come from separate tables that each have their own relationship to the sales table)
 
Product NameLINEAMOUNTCREATEDDATETIMERegion
A5001/3/2020South
A8002/27/2020South
A6001/18/2020East
A6503/4/2020East
B3007/8/2019West
B4004/3/2020West
B8005/6/2019East
B90012/22/2019East
C2009/21/2019North
C7002/10/2020North
C5008/12/2019South
C45011/2/2019South

 

Desired Result:

 

Product NameRegionLast Sale
ASouth800
AEast650
BWest400
BEast900
CNorth700
CSouth450

 

Realistically I have way more than 3 products, this is just a simple layout of how my data is set up. Any help would be greatly appreciated

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@jakeudy here you go

 

Recent Sales by Product and Region = 
VAR __currentDate = 
CALCULATE ( 
    MAX  ( ProductRegion[CREATEDDATETIME] ), 
    ALLEXCEPT( 'Product','Product'[Product Name] ) ,
    ALLEXCEPT( Region, Region[Region] ) 
)
RETURN CALCULATE ( SUM ( ProductRegion[LINEAMOUNT] ), ProductRegion[CREATEDDATETIME] = __currentDate )

 

and here is the output

 

image.png

 

Would appreciate Kudos 🙂 if my solution helped.



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.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@jakeudy here you go

 

Recent Sales by Product and Region = 
VAR __currentDate = 
CALCULATE ( 
    MAX  ( ProductRegion[CREATEDDATETIME] ), 
    ALLEXCEPT( 'Product','Product'[Product Name] ) ,
    ALLEXCEPT( Region, Region[Region] ) 
)
RETURN CALCULATE ( SUM ( ProductRegion[LINEAMOUNT] ), ProductRegion[CREATEDDATETIME] = __currentDate )

 

and here is the output

 

image.png

 

Would appreciate Kudos 🙂 if my solution helped.



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.

This worked great, thank you!

sturlaws
Resident Rockstar
Resident Rockstar

Hi @jakeudy,

assuming you are trying to create a measure you can use in a table visual, you can try this:

Last Sale =
VAR _product =
    CALCULATE ( SELECTEDVALUE ( sales[Product Name] ) )
VAR _region =
    CALCULATE ( SELECTEDVALUE ( sales[Region] ) )
VAR _maxDate =
    CALCULATE (
        MAX ( sales[CREATEDDATETIME] );
        FILTER (
            ALL ( sales );
            sales[Product Name] = _product
                && sales[Region] = _region
        )
    )
RETURN
    CALCULATE (
        SUM ( sales[LINEAMOUNT] );
        FILTER (
            ALL ( sales );
            sales[CREATEDDATETIME] = _maxDate
                && sales[Product Name] = _product
                && sales[Region] = _region
        )
    )

 

Cheers,
Sturla

Greg_Deckler
Super User
Super User

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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