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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Dax - using calculate, filter and lastdate

Hi there,

 

I have a column of values called PF1.1.totalTime. I also have a column of dates corresponding to each value, this column is called UTC Finish Time. The column of dates has many repeated entries, as I have many entries in PF1.1totalTime per day. I want to perform percentile calculations only on the latest day of data, and I've been trying to use the LASTDATE() function to achieve this. Here is what I have so far:

Measure = CALCULATE(PERCENTILE.INC('All PF1 Data (2)'[PF1.1.totalTime],0.95), FILTER('All PF1 Data (2)', 'All PF1 Data (2)'[UTC Finish Time] = LASTDATE('All PF1 Data (2)'[UTC Finish Time])))

However, this seems to calculate the 95th percentile across all dates. In my data, the most recent date is 26th September 2019. If I use lastdate() just on the column of dates and display as a card, it returns the correct value (26/09/2019 00:00:00).When I replace lastdate() with date(2019,/09,26) I get the correct value. Why would my use of LASTDATE() not work here? Is it because it also returns a time of 00:00:00? Many thanks.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@Anonymous you are getting caught into ROWCONTEXT, you need to understand why lastdate works when you throw on visual card but in DAX. Can you post sample data here and expected output and that will help to provide the solution. 

 

Do you want LASTDATE across full dataset? Try following measure

 

Measure = 
VAR __lastDate=CALCULATE(  LASTDATE('All PF1 Data (2)'[UTC Finish Time]), ALL() )
RETURN
CALCULATE(PERCENTILE.INC('All PF1 Data (2)'[PF1.1.totalTime],0.95), 
 'All PF1 Data (2)'[UTC Finish Time] = __lastDate)


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

3 REPLIES 3
parry2k
Super User
Super User

@Anonymous you are getting caught into ROWCONTEXT, you need to understand why lastdate works when you throw on visual card but in DAX. Can you post sample data here and expected output and that will help to provide the solution. 

 

Do you want LASTDATE across full dataset? Try following measure

 

Measure = 
VAR __lastDate=CALCULATE(  LASTDATE('All PF1 Data (2)'[UTC Finish Time]), ALL() )
RETURN
CALCULATE(PERCENTILE.INC('All PF1 Data (2)'[PF1.1.totalTime],0.95), 
 'All PF1 Data (2)'[UTC Finish Time] = __lastDate)


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 solution is awesome! I was getting lost trying to figure out how to take the last date from my DIM table and perform a CALCULATE function with another filter and aggregation on my FACT table. This worked like a charm. Thanks! - Ben

Anonymous
Not applicable

Thank you @parry2k! Could you explain the difference in what I did compared to what your formula does? Struggling to differentiate the logic. Many thanks.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors