Forum Discussion
Dax - using calculate, filter and lastdate
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)
Super UserAnonymous 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)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.
- novicepbixer
Advocate I
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
