cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pandadev
Post Patron
Post Patron

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

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]))
1 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:

32.PNG

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:

33.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

View solution in original post

5 REPLIES 5
amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

 

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:

32.PNG

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:

33.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

View solution in original post

Thanks Giotto , that worked perfectly

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]    ))

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors