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
Harris021
Regular Visitor

showing the highest date from the date columns

Hi all, 

 

I have a SharePoint list with two columns, Company and Report date. Each company can have a few report dates. Using power bi, how can I show only the maximum date from the list of dates for each company. from below image you can see when I select a company it shows all the available report dates and I only want to show the latest date or have adifferent color.Dates.PNG

 

 

2 REPLIES 2
isabella
Employee
Employee

Hi @Harris021 

I did a local test ,you can take a reference 

I Create 2 measure ,one is to calculate the maxdate per company,the other one is to calculate the count for the maxdate

 

Here is my test  table

 

isabella_0-1656399184055.png

 

Measure for maxdate: maxdate = CALCULATE(MAX('Table (3)'[date]), ALLEXCEPT('Table (3)','Table (3)'[company]))

 

 

isabella_1-1656399184056.png

 

 

 

Measure for sales  :salesformaxdate = CALCULATE(SUM('Table (3)'[sales]),FILTER('Table (3)','Table (3)'[date]=MAX('Table (3)'[date])))

 

Then in visualization:

 

 

isabella_2-1656399184057.png

 

 

isabella_3-1656399184058.png

 

 

When only select company A

isabella_4-1656399184060.png

 

 

Best Regards,

Community Support Team _Isabella

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

danextian
Super User
Super User

Hi @Harris021 ,

 

You can create a calculated column to determine whether a date is the max date for a particular item and use this to filter a measure, a visual, a page or the entire report. Example:

Max Date = 
CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Company] ) ) = 'Table'[Date]

danextian_0-1655880590405.png

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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.