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, I need help to find the minimum of one set of values and last of another set of values. I tried min/max formula but cannot figure out how to do min/last. For ex) in order AB0057 I would like to pull 3/29 as my new minimum date from the Old Value, and 4/19 as my new date from the New Value (not 4/26 because it was created on 3/20 which is not the latest. Can someone please help me?
Solved! Go to Solution.
@tracyhopaulson - This following DAX Calculated Column is working for me. It first calculates the max CreateDate for the relevant Order. Then it finds the rows that have that Order and CreateDate and finds the max NewValue.
New Date = var maxcreate = Calculate( max('Table1'[CreateDate]), ALLEXCEPT('Table1','Table1'[Order]) ) return CALCULATE( max('Table1'[NewValue]), ALLEXCEPT('Table1','Table1'[Order]), 'Table1'[CreateDate] = maxcreate )
Result:
Hope this helps,
Nathan
For the latest date, a Calculated Column could look like this:
Maybe I'm doing something wrong and didn't explain clearly but I do not get “4/19” result as the new date, instead I have 4/26 which is the max date from NewValue column, or 3/27 from CreateDate column.
I need the date in last row of column #3 for each order group. The example only show 1 order group. Does this make sense?
@tracyhopaulson - This following DAX Calculated Column is working for me. It first calculates the max CreateDate for the relevant Order. Then it finds the rows that have that Order and CreateDate and finds the max NewValue.
New Date = var maxcreate = Calculate( max('Table1'[CreateDate]), ALLEXCEPT('Table1','Table1'[Order]) ) return CALCULATE( max('Table1'[NewValue]), ALLEXCEPT('Table1','Table1'[Order]), 'Table1'[CreateDate] = maxcreate )
Result:
Hope this helps,
Nathan
@Anonymous Thank you Nathan, your suggestion works for me. You're a STAR!
Hi,
Look at my solution. I think it answers the requirments.
There are many options.
Look at the following links
https://blogs.msdn.microsoft.com/lukaszp/2015/08/08/finding-the-latest-date-in-power-bi-desktop/
Ta
Actually, for each of the "Order" group, I want to return the last value from the column "NewValue", based on the latest "CreateDate". Note: I'm not looking for the latest date in column NewValue.
Hi,
Here you go, some additional help:
Thank you and one more thing... my example shows only 1 order but there are several orders involved here too, so how do I get the last NewValue from each order group?
Go to Edit Queries and then you can use the group by functionality.
This will result with only N rows table, where N = number of orders in the table.
Then add the columns to the original table.
Do some DAX comparison calculations.
See attached pbix - https://we.tl/t-NPX0aTRlrd
Enjoy!
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 |