Setting variable dates as filters

[Anonymous]

**Problem resolved but post can't be deleted**

I'm having an issue with filtering values using variable dates.

Currently my formula is as follows:

Measure=

VAR MinYear = CALCULATE(MIN(RawData[date]), RawData[Type] = "After")

VAR MaxYear = CALCULATE(MIN(RawData[date]), RawData[Type] = "After") + (SELECTEDVALUE(Years[Years])*365)

RETURN

CALCULATE(COUNT(RawData[Ref]), RawData[date] >= MinYear && RawData[date] <= MaxYear )

If I check each variable, I get the correct datestamp for the two variables, similarly I get the correct answer if I replace the variables in the calculate function with " date(XX,XX,XX) " but for whatever reason it doesn't work with the variable.

When testing, the variables show timestamped dates, so I have tried setting the data type to timestamp in the query editor but this doesn't make a different. Any suggestions?

[v-lionel-msft]

Hi @Anonymous ,

Is this what you want?

COUNT(RawData[Ref])
  when RawData[date] >= MinYear && RawData[date] <= MaxYears

Could you show me a sample data model? I need to know the relationship between 'RawData' table and 'Years' table.

Best regards,
Lionel Chen

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

[JarroVGIT]

Hi @Anonymous , 

I see you reference a non-existing variable (MinYearPost) in your return statement, that doesn't seem right?

Furthermore, what is the context this measure is evaluated in? is it part of a visual (and what visual)? For example; could it be there are multiple values in Year[Years] at time of evaluation and hence the SELECTEDVALUE() will not return anything? 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

Whoops, I had changed the variable names in the post so it would make more sense out of context, but clearly forgot to change the variable names in the CALCULATE function!

It should show:

Measure=

VAR MinYear = CALCULATE(MIN(RawData[date]), RawData[Type] = "After")

VAR MaxYear = CALCULATE(MIN(RawData[date]), RawData[Type] = "After") + (SELECTEDVALUE(Years[Years])*365)

RETURN

CALCULATE(COUNT(RawData[Ref]), RawData[date] >= MinYear && RawData[date] <= MaxYear )

Selected value is based on a number slicer, so the user selects a number between 1-5 which will be *365 to get the number of days in a year (though it doesn't account for leap years)

[v-lionel-msft]

Hi @Anonymous ,

Very weird, using your formula I can return the correct result.

Measure = 
VAR x = 
CALCULATE(
    MIN(RawData[Date]),
    FILTER(
        RawData,
        RawData[Type] = "after"
    )
)
VAR y = x + [Years Value]*365
RETURN
CALCULATE(
    COUNT(RawData[Ref]),
    FILTER(
        RawData,
        [Date] >= x && [Date] <= y
    )
)

(Fact table)

(What IF parameter)

Could you provide more information about the problem you have met?

Best regards,
Lionel Chen

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

[Anonymous · Accepted solution]

I've actually managed to fix this!

Turns out I had a filter being applied via another slicer which was changing the date ranges and therefore not applying the assumed window.

Thanks anyway all!

[v-lionel-msft]

Hi @Anonymous ,

If your problem has been solved, please consider Accept it as the solution  to help the other members find it more quickly.

Best regards,
Lionel Chen