Filter rows where SELECTEDYEAR() is in between two columns

[Anonymous]

Help!

I have a hard time solving this, from a programming perspective, easy task in DAX.

What I want to solve:

1. User selects a year from a slicer. This year comes from a date table

2. All rows where the selected years are in between FromDate and ToDate should be returned. 

3. From here, I want to DISTINCTCOUNT() an ID and display them in different visuals, using different categories, accross the page.

What I've tried so far:

M =
CALCULATE(
    DISTINCTCOUNT('datastore X'[ID]),
    FILTER (
        ALL('datastore X'[FromYear], 'datastore X'[ToYear], 'datastore X'[ID]),
        'datastore X'[FromYear] <= [SelectedYear] &&
        'datastore X'[ToYear] >= [SelectedYear]
    )
    )

Where [SelectedYear] = SELECTEDVALUE('datastore Y'[Year]).

The problem with this measure is that if the user selects e.g. 2023, this filter on the table will first be applied before the measure is executed.

Any help is appreciated!

[Anonymous]

Hi @Anonymous ,

You can try to modify your formula with reference to the following:

M_ = 
CALCULATE(
    DISTINCTCOUNT('datastore X'[ID]),
    FILTER(
        'datastore X',
        'datastore X'[fromYear] <= [SelectedYear] &&
        'datastore X'[toYear] >= [SelectedYear]
    )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Adam,

THank you for your response, I appreciate it.

Your solution is almost correct, however in your example with the provided measure M_, the person with ID 3 will not show up in the table if the selected year is 2024. In my case, I need that to happen as the ID 3 person was active 2024 given its to and from date (2023 and 2025).

Do you have any suggestion on how to fix that?

Best,

Aksel

[timalbers]

Hi @Anonymous ,
in order to help you with this, I will need to have some more information about how your model is set up. 
Especially on how the date table is linked to your fact table and also what type of data exactly is in the fromDate and toDate columns. Years or dates?

[Anonymous]

Hi, 

Thanks for the fast reply

In fromDate and toDate, as well as date column in date table are of typ date 

The relationship between fromDate and date is active

The relationship between toDate and date is inactive

 These are the once I am using in my relationship. 

What I forgot to provide is details on how I extract the YEAR. 

fromDate and toDate have two respective calculated columns, called fromYear and toYear. These are the ones I am using in my measure. 

I will make sure to edit that in the original post.

[timalbers]

Ok, if you are only comparing years, and not whole dates, then something like this probably would already work:

M =
    VAR min_year =
        MIN( date_table[Year] )
    VAR max_year =
        MAX( date_table[Year] )
    RETURN
        CALCULATE(
            DISTINCTCOUNT( 'datastore X'[ID] ),
            'datastore X'[FromYear] <= min_date
                && 'datastore X'[ToYear] >= max_date
        )

[Anonymous]

I appreciate your help and I think this is a good start, however records that have
FromYear < Selected year > ToYear

So, e.g., if selected year is 2023, records that have
FromYear: 2018
ToYear: 2019

are still included in the resulting table visualisation.