Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Get the count between two date columns

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...
Need Help
poweringthru's avatar
poweringthru
Helper I
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      poweringthru
      Helper I
      3 years ago

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