Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Not applicable

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

Helper I
Helper I

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 =

VAR Min_Date =

VAR Check_Value =
    && (SELECTEDVALUE('Table1'[endingdate])>=Min_Date || ISBLANK(SELECTEDVALUE('Table1'[endingdate]))),




Not applicable

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.

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

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors