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(Raw...
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!

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      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!

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

        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