Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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)
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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |