Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pandadev
Post Prodigy
Post Prodigy

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
Super User

@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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.