Filtering data based on last created

[richrich123]

Morning,

I'm a bit stumped on this, it seemed easy at first glance of the request but maybe not.

I have a table with all vacancies by client. I want to see a table of the clients who have not had a vacancy created in the last 12 months. So as in this test data sample below, Drawing Enterrpise have added a job in the last 12 months so can be excluded but Farmouse Trading haven't had any new jobs added in the last 12 months so would be listed

Many Thanks

[v-jingzhang · Accepted solution]

Hi @richrich123 

Did you solve this problem? If not, this is a DAX method you can use. 

Create the following measure and add it on the table visual as a visual-level filter. Set its value to 1. This measure will get the last vacancy created date for every client and compare it to the date last year (based on today).

Flag = 
var _lastDate = CALCULATE(MAX('Table'[Vacancy Created]),ALLEXCEPT('Table','Table'[Client]))
return
IF(_lastDate < EDATE(TODAY(),-12), 1, 0)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[richrich123]

Hi @Vijay_A_Verma ,

Many thanks for you help. I have tried that and ammended the source (if I have isolted the source part correctly, I swapped this out:

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7BDcAgCAXQXTybIGjT9qhVlzDuv0YBTWjSyz88yIcxXHbeZYkD8AYKhG76zYUDAS/hoCxSZBY3o7FEglOUTB8OArSKylDzr1m56DKZNob2vbe4s/Ss92KwN5RXB6bN8wU=", BinaryEncoding.Base64)

Must have something wrong still? I just dropped the new query into a table but just see the code.

Thanks

[Vijay_A_Verma]

Do like this

Import from your source

Go to Advanced editor and delete everything after source line (make sure to put a comma after the source line).

Now copy my code from #"Changed Type" till end

Paste into Advanced editor

[Vijay_A_Verma]

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7BDcAgCAXQXTybIGjT9qhVlzDuv0YBTWjSyz88yIcxXHbeZYkD8AYKhG76zYUDAS/hoCxSZBY3o7FEglOUTB8OArSKylDzr1m56DKZNob2vbe4s/Ss92KwN5RXB6bN8wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Vacancy = _t, #"Vacancy Created" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Vacancy", type text}, {"Vacancy Created", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Client"}, {{"MaxDate", each List.Max([Vacancy Created]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Date.AddYears(Date.From(DateTime.FixedLocalNow()),-1)<=[MaxDate]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Client"}, #"Filtered Rows", {"Client"}, "Filtered Rows", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Filtered Rows"})
in
    #"Removed Columns"