Forum Discussion

REWINDER's avatar
REWINDER
Frequent Visitor
4 years ago
Solved

Measure to filter rows based on two dates (begin and end date)

Hi everyone,
I have been working on this problem for a couple of hours now and hope someone is able to help me solve this:

Looking to get a measure with Active verhicles (>=Purchase date , <=Replacement date).
Measure should be able to get a graph with active number of verhicles per year 2007=number,2008=number,2009=number, etc.
And used as a reportcard to say number of current active vehicles.


My two tables

 

The data model

 

This is the dax I got so far, (Note how it will filter on purchase dates, and not on active between date)

 

#Vehicles Active = 
        COUNTX(FILTER(FleetActivity, FleetActivity[PurchaseDate]<= RELATED('Date'[Date]) && FleetActivity[ReplacementDate] >= RELATED('Date'[Date])), FleetActivity[ClassStructure])

 

 
I thank you for any help I can get

measure
Two dates
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your data model correctly, but please try the below measure and put it together with Date[Year] column from Date Table.

     

    #Vehicles Active =
CALCULATE (
    COUNTROWS ( FleetActivity ),
    FILTER (
        ALL ( FleetActivity ),
        FleetActivity[PurchaseDate] <= MAX ( 'Date'[Date] )
            && FleetActivity[ReplacementDate] >= MIN ( 'Date'[Date] )
    )
)

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your data model correctly, but please try the below measure and put it together with Date[Year] column from Date Table.

     

    #Vehicles Active =
CALCULATE (
    COUNTROWS ( FleetActivity ),
    FILTER (
        ALL ( FleetActivity ),
        FleetActivity[PurchaseDate] <= MAX ( 'Date'[Date] )
            && FleetActivity[ReplacementDate] >= MIN ( 'Date'[Date] )
    )
)
  • REWINDER's avatar
    REWINDER
    Frequent Visitor
    4 years ago

    Thank you Jihwan_Kim,

    That works great!
    Just applied it to the actual model and works too.

    Jihwan_Kim  > If I want to retain the column data to see the breakdown of what ClassStructure the vehicles had, how would I adapt the measure for that?

    Thank you in advance