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

[jakeudy]

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 Name	LINEAMOUNT	CREATEDDATETIME	Region
A	500	1/3/2020	South
A	800	2/27/2020	South
A	600	1/18/2020	East
A	650	3/4/2020	East
B	300	7/8/2019	West
B	400	4/3/2020	West
B	800	5/6/2019	East
B	900	12/22/2019	East
C	200	9/21/2019	North
C	700	2/10/2020	North
C	500	8/12/2019	South
C	450	11/2/2019	South

Desired Result:

Product Name	Region	Last Sale
A	South	800
A	East	650
B	West	400
B	East	900
C	North	700
C	South	450

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

[parry2k · Accepted solution]

@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

Would appreciate Kudos 🙂 if my solution helped.

[jakeudy]

This worked great, thank you!

[sturlaws]

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]

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