count row on condition

[brechtt]

I'm having some difficulties with a DAX query. I have this table:

Name (string)	In use (date)	Out of use (date)
Car 1	01/01/2020	06/05/2021
Car 2	01/06/2020	null
Car 3	05/09/2021	04/08/2030
I have a table with cars and a date table. I want to create a measure that I can use to draw a Column chart. On the X-axis we have the quarters, on the y-axis the total numbers of cars that are in use on the last day of the quarter. This is what I have but it does not seem to work correctly. I'm struggling with the today()
Active assets = CALCULATE( COUNTROWS('Fleet'), 'Fleet'[In use ] <= today() && 'Fleet'[Out of use] >= today() )​

[brechtt]

The result is not correct, it seems like I got only the newly active assets in the period, not the accumulated active assets

[AnthonyGenovese]

I don't understand what you mean. With the details you already provided, what rows are being counted and what rows are being excluded?

[brechtt]

I'm starting to understand what goes wrong. In my model I have a relationship between the In use column and the date column of my datetime tabel. So when I create a barchart with the data on the x-axis, the data is automatically filtered. I only get to see the number of cars where the In Use data is the same as the X-axis label. I don't want that, for every label on the x-axis, I want to visualize the number of cars that are active (meaning a-axis label is between In Use and Out Of Use)

[AnthonyGenovese]

What about something like

 CALCULATE( COUNTROWS('Fleet'), ALL(DateTable), 'Fleet'[In use ] <= today() && 'Fleet'[Out of use] >= today() )​

[brechtt]

Now I have a constant value over the entire timerange, I suppose it's not filtering at all anymore. I don't understand the today(). It shouldn't be today(), it should be the month, year, ... of the x-axis

[AnthonyGenovese]

Then something like

var dateFilter=selectedvalue(dateTable[date])

var result= CALCULATE( COUNTROWS('Fleet'), ALL(DateTable), 'Fleet'[In use ] <= dateFilter && 'Fleet'[Out of use] >= dateFilter)​

return result 

[brechtt]

This doesn't return anything at all

[AnthonyGenovese]

You will need to provide more concrete details then, showing relationships, table metadata, table data, the chart you are using, and how it is configured. The last code I sent you takes a look at the xaxis, then counts the rows for the fleet table by first removing the xaxis filter, but then applying it to the two columns. 

[brechtt · Accepted solution]

this solved it:

Active assets = 
var endDate = MAX(DateTime[Date])

var result = 
CALCULATE(
    COUNTROWS('fleet'),
    REMOVEFILTERS('DateTime'),
    'fleet'[in use] <= endDate,
    'fleet'[out of use] > endDate
    ||
    ISBLANK('fleet'[out of use])
)

return result

[AnthonyGenovese]

Nice. That is really close to what I wrote (good on you for accounting for blank), I wonder why yours worked and mine didn't!

[AnthonyGenovese]

What isn't working about it? Can you provide more details?