Forum Discussion
Get data based on other columns
Hi,
I have posted this once before and accepted the answer, however it was found to then not work properly. It involved filtering for "Completed", then grouping columns. However, this shows me data for all users and their statuses, whereas I only need it for those NOT showing "Completed" in the Status column.
******
I hope someone can help with this PBI query.
I need to get a column of data in a table showing the following query…
If Status 'does not equal' "Completed”,
Give me the date of the previous "Reg. Date" of the same "Content"
Test Table
So, in the example above, for Bob it would show 21/06/23
For Martha it would show 04/08/23
I then need to only show those more than a year before [today], so it would only show Bob’s in the example above, however I guess a simple filter on the column would do that.
Any help appreciated.
Thanks all,
Rich
Thanks for the reply from vicky_ , please allow me to provide another insight:
Hi RichJW ,Here are the steps you can follow：
1. Create calculated column.
Test = IF( 'Table'[Status]<>"Completed", CALCULATE(MAX('Table'[Reg.Date]), 'Table'[Reg.Date]<EARLIER('Table'[Reg.Date]), ALLEXCEPT('Table','Table'[Content],'Table'[User])))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Try this in a calculated column:
Previous Reg = IF('Table'[Status] <> "Completed", CALCULATE(MAX('Table'[Reg Date]), FILTER('Table', 'Table'[Reg Date] < EARLIER('Table'[Reg Date]) && 'Table'[Content] = EARLIER('Table'[Content])) ))Thanks for the reply from vicky_ , please allow me to provide another insight:
Hi RichJW ,Here are the steps you can follow：
1. Create calculated column.
Test = IF( 'Table'[Status]<>"Completed", CALCULATE(MAX('Table'[Reg.Date]), 'Table'[Reg.Date]<EARLIER('Table'[Reg.Date]), ALLEXCEPT('Table','Table'[Content],'Table'[User])))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
