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.
I am trying to create a new column on a table that is a Max calculation with a filter- in this instance, I need to return the Max Date but only if the Rate Change was grater than 0 (example table below).
Right now I am using an equation that returns the Max Date for Each ID, but I need to filter that return to only be if that date was associated with an increase
Current equation-
ID | Date | Rate Change | Current Date returned | Most Recent Rate Change (DESIRED RESULT) |
1 | 7/3/19 | 0% | 7/3/19 | 6/8/19 |
1 | 6/8/19 | 4% | 7/3/19 | 6/8/19 |
1 | 5/1/18 | 3% | 7/3/19 | 6/8/19 |
2 | 8/8/19 | 2% | 8/8/19 | 8/8/19 |
2 | 2/17/18 | 7% | 8/8/19 | 8/8/19 |
I have tried to build the filter into the equation in multiple ways, and keep failing, I need it as part of the equation rather than a filter on a visual or table becuase it will be built used
Solved! Go to Solution.
Hi @ehmacc ,
You can use the following formula:
Most recent rate change (calculated) =
CALCULATE(LASTDATE('EmploymentHistory'[Date]), ALLEXCEPT('EmploymentHistory','EmploymentHistory'[ID]), 'EmploymentHistory'[Rate Change]>0)
Basically I added one more condition to the CALCULATE filter: rate should be higher than 0.
Here is a screenshot of what it looks like.
Finally, here is a Power BI file with the new formula.
Does this help you? Let me know if you have further questions,
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
Hi @ehmacc ,
You can use the following formula:
Most recent rate change (calculated) =
CALCULATE(LASTDATE('EmploymentHistory'[Date]), ALLEXCEPT('EmploymentHistory','EmploymentHistory'[ID]), 'EmploymentHistory'[Rate Change]>0)
Basically I added one more condition to the CALCULATE filter: rate should be higher than 0.
Here is a screenshot of what it looks like.
Finally, here is a Power BI file with the new formula.
Does this help you? Let me know if you have further questions,
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
Hi @ehmacc ,
You were almost there. Just needed a condition. Let me know if this works for you...it is a measure
Most Recent Rate Change = CALCULATE(MAX(EmploymentHistory[Date]),Filter(ALLEXCEPT(EmploymentHistory,EmploymentHistory[ID]),EmploymentHistory[Rate Change]<>0))
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Thank you so much!
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 |