Number of Entries within 1 year of date specified

[Domearian]

Hello, Previously I used this code to identify if a SAP No appeared in a previous row:

COUNT(SELECT(DOGWEED TESTS AND SEARCHES[SAP No], AND([_ROWNUMBER] <= [_THISROW].[_ROWNUMBER], [SAP No] = [_THISROW].[SAP No])))

I would like to add another condition that would check the same as above but within the last 365 days of a specified date. The date would be the date of the row which in this case would be [Date of Test].

Thank you in advance.

[Suvrutt_Gurjar · Accepted solution]

Presuming the [Date of Test] is in each row of the DOGWEED TESTS AND SEARCHES table, please try

COUNT(SELECT(DOGWEED TESTS AND SEARCHES[SAP No], AND([_ROWNUMBER] <= [_THISROW].[_ROWNUMBER], [SAP No] = [_THISROW].[SAP No], [Date of Test]>=TODAY()-365 )))

Alternatively, you could evaluate creating a slice on the table DOGWEED TESTS AND SEARCHES with a filter expression [Date of Test]>=TODAY()-365 and then apply the other criteria on that slice to reduce number of records being evaluated by the SELECT() statement.

COUNT(SELECT(Slice Name[SAP No], AND([_ROWNUMBER] <= [_THISROW].[_ROWNUMBER], [SAP No] = [_THISROW].[SAP No])))

[Domearian]

That does work perfectly. Thank you. I was concerned about using the -365 but it does work perfectly. I am also looking into the slice option also.

Many Thanks Suvrutt.

[Suvrutt_Gurjar]

Good to know it helps. You are welcome.