Dax - using calculate, filter and lastdate

[Anonymous]

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.

[parry2k · Accepted solution]

@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)

[novicepbixer]

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]

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.