Filter step 1 of process data

[Leslee]

Im having trouble figuring out how to filter my dataset correctly.

I have 1 table containing invoice data. 

The following columns are in the data set

1. ID

2. Documentnumber <- unique ID for each invoice

3. Activity <- there are 5 different steps in our invoice process which are labeled

1. Invoice date
2. Scan date
3. Start workflow
4. Book
5. Paid

3. Timestamp <- Timestamp of related activity

I want to be able to filter my data set on timestamp and a specific value of my activity column. For instance, all invoices with a invoice date in the last three months. It should not filter out rows based on other activities that happen prior to the last three months. For instance:

Invoice 1 (filtered) :

Invoice timestamp : 1 aug 2022

Scan date : 5 aug 2022

Ïnvoice 2 (should not be filtered, result of instable process) :

Invoice timestamp: 10 nov 2022

Scan date: 1 aug 2022

Due to the unstable process, not all steps are done in the correct order. This is something for the business to improve while also lowering the overall throughput time. 

[johnt75]

If you need the time part of the timestamp column for other things then split that column into 2, a date part and a time part. You can do this in power query. If you don't need the time part then simply change the type of the column to Date in power query.

Once you have a date column, set up a proper date table and link that to your data. You can then use columns from your date table on slicers and also set up a slicer on the activity type.

[Leslee]

Hi Johnt, thank you for your time.

I've split the timestamp and added a date table. However, if I were to put a slicer on my activity type, I'll only see the specific activity.

I want to see all invoices with an invoice date > 1 okt 2022.

And show me all related activities, even if e.g. actitivy "scanning invoice" were to be < 1 okt 2022.

Invoice 1 (filtered) :

Invoice timestamp : 1 aug 2022

Scan date : 5 aug 2022

Ïnvoice 2 (should not be filtered, result of instable process) :

Invoice timestamp: 10 nov 2022

Scan date: 1 aug 2022

[johnt75 · Accepted solution]

You could create a measure like

Activity is visible =
var startDate = MIN('Date'[Date])
var endDate = EDATE(startDate, 3)
var filteredInvoices = CALCULATETABLE( VALUES('Table'[Document no]), REMOVEFILTERS('Table'), 'Table'[Activity] = "Invoice date", 'Table'[Date] >= startDate && 'Table'[Date] < endDate)
return IF ( SELECTEDVALUE('Table'[Document no]) IN filteredInvoices, 1, 0)

Make sure that there is no relationship between your Date table and your data table, then use the new measure as a visual level filter