Get data based on other columns

[RichJW]

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

[Anonymous · Accepted solution]

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

[RichJW]

Hi @Anonymous ,

This works beautifully. Thanks for your help.

Rich

[RichJW]

Hi @Anonymous,

Apologies, I have been on annual leave.  Thank you for the advice, I will try to test today and reply back.

Many thanks,

Rich

[RichJW]

Thanks @vicky_ , so close to what I need.

I've tried the above formula and it just seems to be missing the part where it takes into account the User. With this formula it is giving me the previous date of anyone's Reg Date, whereas I only want it for the same individual.

Thanks, Rich

[vicky_]

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]))
))