Get count of ID's where departure_date is greater than event_date , not showing correctly

[Pandadev]

Hi I am trying to filter out all rows of data where there are less than or equalt to 2 matched ID_Code which has a date greater than Event Code.

I have multiple rows of data with many matching ID , all with dates , and I only want to get a count of dates after the event date.

So every row which has ID Code 001 , and has another 4 rows of data with ID Code 001 , that has a date greter than the event date , then show that total . Each ID Code , will have the same event date. 

I was looking for something like this

ID          Event Date             departure date

001        10/03/2020            01/02/2020   

001        10/03/2020           11/03/2020

001        10/03/2020           12/03/2020

001        10/03/2020           16/03/2020   

My attempted column formula is not working , I wanted the number 3 to be in every row of the Total Column for ID 001

Total = (CALCULATE(COUNT('TU Feed'[ID]),FILTER('TU Feed','TU Feed'[departure_date]>'TU Feed'[Event Start Date]))

[amitchandak]

@Pandadev 

The formula seems right. do you have any filter on page

Check like this

Total = CALCULATE(COUNT('TU Feed'[ID]),FILTER(all('TU Feed'),'TU Feed'[departure_date]>'TU Feed'[Event Start Date]))

This will remove filter

[Pandadev]

the page I have the data showing is showing the latest departure date for each ID , when I add the column with the formula it shows the number 1 , even though there are more than one occurrence of the departure date greater than the event date. what my end goal is , is to only show the ID's latest date , where there has been 2 or more instances of that ID having a departure date greater than the event date , as I dont want to see the ones that just have 1 instance. i was hoping to add this measure to the page filter , to filter them out.

[v-gizhi-msft · Accepted solution]

Hi,

You can not add measure to page filter pane, so please try this column instead of measure:

Column = 
CALCULATE (
    COUNT ( 'Table'[ID] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[ID] = EARLIER ( 'Table'[ID] )
            && 'Table'[departure date] > 'Table'[Event]
    )
)

In my test sample, the result shows:

And then you can add this column to page filter to show the ID's(which count of depature date>event date is equal or more than 2) latest date:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[Pandadev]

Thanks Giotto , that worked perfectly

[Pandadev]

Is it possible to amend this so it looks at departure date that is greater than the last 7 days

Column = CALCULATE (    COUNT ( 'Table'[ID] ),    FILTER (        ALLSELECTED ( 'Table' ),        'Table'[ID] = EARLIER ( 'Table'[ID] )            && 'Table'[departure date] > 'Table'[Event]    ))