Get the count between two date columns

[Anonymous]

I'm having a hard time wrapping my head around this.

Each row has two date columns (start date and end date). If one row has a date starting 1/1/2023 and end date of 5/1/2023 then the time period between the two dates is when it was considered active and I'd like to tally a "1". If it has a start date but no end date, that's also a tally for "1".

So what I ultimately want to see in the bar (or line) graph, using the start and end date above, is that if I'm looking at the month of Jan, Feb, Mar, Apr then it should show me the count of "1". If it had a start date of 1/1/2023 and no end date, and I was looking at the graph in Dec then it should also count as 1 and it should count as 1 for all the months before up till the start date.

Can't figure out how to do this.

[poweringthru]

Try this measure (assumes you have 'Calendar' table with a 'Calendar'[Date] column, and your matrix column or your line chart x-axis is this Calendar column).

Also, SELECTEDVALUE needs that each row in your table can have only one startingdate value and only one endingdate value.

Is Active Measure =

VAR Max_Date =
MAX('Calendar'[Date])

VAR Min_Date =
MIN('Calendar'[Date])

VAR Check_Value =
IF(
    SELECTEDVALUE('Table1'[startingdate])<=Max_Date
    && (SELECTEDVALUE('Table1'[endingdate])>=Min_Date || ISBLANK(SELECTEDVALUE('Table1'[endingdate]))),
    1,
    0
)

RETURN
Check_Value

[Anonymous]

I do have a start and end date columns and a date table. I applied what you showed but when inserted into the line graph, I get a single line with the value of 1.

[poweringthru]

Make sure to add your Table1 rows as legends in the chart (that way it will show a different color line for each one).

Also, try it on a matrix (Table1 x Calendar and this measure as Value) and you'll see it calculates the 1 and 0 as expected).