Count Rows - using conditions in two date columns

[Kmcdonald]

Hi,

I have a table where I'm tring to figure out any number of events on open on each day. 

I have created a calendar table with the formula below:

Next I have created relationships with this new table's "Date" column to the "Start Date" and Event Disposition Date (All), which are the open and closed dates of each event.

I'm trying to figure out how to write a measure to stay on this QE Date table, where on each given date I'd like to count rows where the date value of the calendar is the number of rows where the "Start Date" is equal or greater than the calendar date and the "Event Disposition Date (All)" is greater than the calendar date. This should given me what number of events were open on any given day to give me a trend I can put into a graph.

When I've had this issue in the past, I've used Power Automate to export and save an excel format of the report daily to Sharepoint and I've used the date created on sharepoint to form this logic. I'm having a go with COUNTIF functions but having no joy as of yet.

Can someone think of a measure that can achieve this please?

[v-xiaotang]

Hi @Kmcdonald 

usually information below is needed, could you provide it? Thanks. 

 (1) a sample file, you can replace raw data with bogus data to protect your privacy.

     or provide some sample data that fully covers your issue/question.

(2) give your expected result based on the sample you provide. If calculation formula is involved, please provide it.

Kindly note: Please ensure the data in sample is concise and representative.

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Kmcdonald]

Sorry also on the counting the "Event Disposition Date (All)", it would also need to count where this field is blank. So greater than the start date and blank. 

[m3tr01d]

Hi @Kmcdonald,

maybe something like this would work

Measure = 
VAR _Date_Showing = MIN( 'QE Date'[Cal Date] )
RETURN
CALCULATE(
	COUNTROWS( 'QE Track and Trend' ),
	'QE Track and Trend'[Start Date] >= _Date_Showing,
	OR(
		'QE Track and Trend'[Event Disposition Date (All)] >= _Date_Showing, 
		ISBLANK('QE Track and Trend'[Event Disposition Date (All)])
	),
	REMOVEFILTERS( 'QE Date' )
)

[Kmcdonald]

Hi @m3tr01d ,

I think I did something wrong, I used your formula and got the result below:

This is the measure I entered:

The number for today should be 51 but it has 2. I filtered the data and it showed 51 fields that were blank in the "Event Disposition Date (All)" date column we are trying to refer to in the measure.

Can you see what the possible issue is?

THanks,

Kurt