Relative Date filter --- not in last x days

[samw5]

I have a bunch of log data for computer scans. Each computer has multiple entries with a field called LastScan. 

I am trying to create a filter (ultimately to show a visual) for computer that have their latest scan over a certain period of time.

So in the example above, I need to know that the "LastScan" for the machine highlighted is 8/25/2020 not the other values and yet retain the rest of the data (ReleaseName). 

I tried to use the GroupBy in the Query Editor, however it makes the the other columns go away. If I add multiple grouping I am not able to get the correct data and end up with an entry for each date which isn't what I am trying to get. 

[v-eqin-msft]

Hi @samw5 ,

According to my understand, you want to highlight the ComputerID when its LastScan is not in last X days (chose by parameter),right?

You could follow these steps or look at the pbix  here

1. Create a what-if parameter for last X days like this:

2. Use the following formulas:

theLastestDate =
CALCULATE ( MAX ( 'Table'[LastScan] ), ALL ( 'Table' ) )

lastest by ComputerID =
CALCULATE (
    MAX ( 'Table'[LastScan] ),
    ALLEXCEPT ( 'Table', 'Table'[ComputerID] )
)

LastScan not in last X days =
VAR _diff = [theLastestDate] - [lastest by ComputerID]
RETURN
IF ( _diff <= MAX ( 'Last X days'[Last X days] ), 0, 1 )

3.Right-click the ComputerID in format Pane and select Conditional formatting -->Background color -->apply rules

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[omelo]

This is a great solution! Thank you 👍

[lkalawski · Accepted solution]

Hi @samw5

Your way to take advantage of Group By was very good. Probably the next step you took was "expand table" and you showed all your records.

Before you do that, add an extra column (step: Added Custom) that will select only the maximum rows - you can use this code:

let
    Source = [your source],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CompID", Int64.Type}, {"ReleaseName", Int64.Type}, {"Column1", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CompID"}, {{"MaxDate", each List.Max([Column1]), type nullable datetime}, {"Details", each _, type table [CompID=nullable number, ReleaseName=nullable number, Column1=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TopDate", each Table.Max([Details],"Column1")),
    #"Expanded TopDate" = Table.ExpandRecordColumn(#"Added Custom", "TopDate", {"ReleaseName", "Column2", "Column3"}, {"ReleaseName", "Column2", "Column3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded TopDate",{"Details"})
in
    #"Removed Columns"

 
I also prepared for you the file with solution. Please find it by using this link: https://gofile.io/d/FRY381

EDIT: file with your data: https://gofile.io/d/QjqJC0

_______________
If I helped, please accept the solution and give kudos! 😀 

[samw5]

That works!

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

You are looking for a PowerQuery solution, correct?

[samw5]

Ultimately I don't really care if the filter is applied in PowerQuery, I'm just trying to create a table in PowerBI Desktop that will show the machines that haven't had a "LastScan" in the last x amount of days. 
Couldn't seem to figure out how to do this with the Releative Filter on the Visual.

ComputerID	ReleaseName	LastScan
g:6755413576635087	1909	2020-08-30T13:04:01.0560000+00:00
g:6755413576635087	KB4566116 (18362.1049)	2020-08-30T13:04:01.0560000+00:00
g:6755413576635087	KB4565483 (18362.959)	2020-08-30T13:04:01.0560000+00:00
g:6755413576635087	2004	2020-08-30T13:04:01.0560000+00:00
g:6755413576635087	KB4565351 (18362.1016)	2020-08-30T13:04:01.0560000+00:00
g:6755413576635087	KB4559004 (18362.997)	2020-08-30T13:04:01.0560000+00:00
g:6755413576635087	1903	2020-08-25T04:33:51.0780000+00:00
g:6755413576635087	KB4560960 (18362.900)	2020-07-16T04:15:07.5150000+00:00
g:6896145144193208	KB4565483 (18362.959)	2020-08-30T13:56:57.6200000+00:00
g:6896145144193208	KB4566116 (18362.1049)	2020-08-30T13:56:57.6200000+00:00
g:6896145144193208	2004	2020-08-30T13:56:57.6200000+00:00
g:6896145144193208	1909	2020-08-30T13:56:57.6200000+00:00
g:6896145144193208	KB4565351 (18362.1016)	2020-08-30T13:56:57.6200000+00:00
g:6896145144193208	KB4559004 (18362.997)	2020-08-30T13:56:57.6200000+00:00
g:6896145144193208	KB4560960 (18362.900)	2020-07-28T19:45:24.4880000+00:00
g:6966508001147294	KB4559004 (18362.997)	2020-08-30T13:06:01.0850000+00:00
g:6966508001147294	KB4565483 (18362.959)	2020-08-30T13:06:01.0850000+00:00
g:6966508001147294	1909	2020-08-30T13:06:01.0850000+00:00

[Stachu]

you could do a measure like this:

NrOfComputersScanned = 
VAR __nrOfDaysInScope = 1
VAR __computers = GROUPBY('Table','Table'[ComputerID],"LatestScan",MAXX(CURRENTGROUP(),[LastScan]))
VAR __validComputers = FILTER(__computers,[LatestScan]>=TODAY()-__nrOfDaysInScope)
RETURN
COUNTROWS(__validComputers

that will limit the computer ids to the relevant ones, you just need to add whatever additional logic you want to calculate

you can change the number of days in scope using the first variable