Counting no of rows when difference between two dates are in certain range

[Dunner2020]

Hi there,

I want to write a measure that counts the number of cancelled events when the difference between the cancelled date of the event and the actual start date of event is less than 24 hours. I have some data inclusion filters that are applied at the page level. I want the measure to calculate the cancelled event on inclusion data only. The date data looks like as follow:

Cancelled Date has a default value of '1/01/1900 1:00:00 pm' which means no cancellation date. I wanted to calculate the no of events that have cancelled date. So my measure is as follow:

Cancellation events =

var cancel_date = MAX('Table'[Cancelled Date ])

Var actual_date = MAX('Table'[Actual Interruption Start Time])

RETURN

COUNTROWS(FILTER('Table', IF(YEAR(cancel_date)>1900,DATEDIFF(cancel_date,actual_date,HOUR)) < 24))

However, the above result did not produce the desired result. It shows blank value when I tried to display on the card visual. I am not sure whether I am calculating in the right manner or not. Any help would be really appreciated.

Data can be download from here 

[amitchandak · Accepted solution]

@Dunner2020 , Create a measure like

calculate(countrows(Table), filter( Table, datediff(Table[Cancelled Date ],Table[Actual Interruption Start Time],hour)<24))