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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
CJANE
Helper I
Helper I

LAST DATE FROM FILTERED DATA

I am having trouble finding a solution to displaying units sold based on last date in a date column.  For example:  I have a data set that includes Trust Symbol, Date Sold, Manufacture, and Units Sold.  I want to display the # of units sold by trust symbol for the last date in the dated sold column.  I included an example of the data set.  I dont want to use a date filter.  I want the report to automatically show the # of units sold by trust.  Based on the data set below I would want the report to display Trust 1 sold 700 units, Trust 2 sold 50 units, and Trust 3 sold 4 units.  All other dates should be ignored.  Any help would be appreciated.

TrustSymbolDate SoldManufactureUnits Sold
Trust 110/16/2017 0:00Red500
Trust 110/16/2017 0:00Orange200
Trust 210/16/2017 0:00Yellow50
Trust 310/16/2017 0:00Green4
Trust A10/10/2017 0:00White825
Trust 210/10/2017 0:00Purple65
Trust 310/6/2017 0:00Dash2
Trust 410/3/2017 0:00Light Green200
Trust 19/25/2017 0:00Magenta125
Trust b9/20/2017 0:00Corn6

 

I want the ability to display the following since 10/16 is the last date in the date sold column.

 

Trust SymboUnits Sold
Trust 1700
Trust 250
Trust 34
1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@CJANE

 

Hi, Try with this measure:

 

 

UnitsSold-LastDate =
CALCULATE (
    SUM ( Table1[Units Sold] ),
    FILTER (
        Table1,
        Table1[Date Sold]
            =  CALCULATE ( LASTDATE ( Table1[Date Sold] ), ALL ( Table1 ) ) 
    )
)

Regards

 

Victor

Lima - Peru

 




Lima - Peru

View solution in original post

2 REPLIES 2
Vvelarde
Community Champion
Community Champion

@CJANE

 

Hi, Try with this measure:

 

 

UnitsSold-LastDate =
CALCULATE (
    SUM ( Table1[Units Sold] ),
    FILTER (
        Table1,
        Table1[Date Sold]
            =  CALCULATE ( LASTDATE ( Table1[Date Sold] ), ALL ( Table1 ) ) 
    )
)

Regards

 

Victor

Lima - Peru

 




Lima - Peru

Thank you!!  Saved me a lot of time and grief.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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