Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Setting variable dates as filters

**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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

6 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        v-lionel-msft
        Community Support
        6 years ago

        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.

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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.