Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Date IF Statement

Hi,    I'm trying to do a simple IF Statment but it doesn't seem to work; I'm trying to created a calculate column to use as a customer filter.    I'm looking at 3 Columns as listed below:   (...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Do you check my answers above?

    these are all measures instead of columns

    max selected = MAX('calendar'[Date])

Measure = IF([max selected]>MAX([start])&&[max selected]<=MAX([end]),"Include","exclude")

     

    Best Regards

    Maggie

nandukrishnavs's avatar
nandukrishnavs
Community Champion
6 years ago

Hi Anonymous ,

 

Slicers cannot be used in calculated columns. Instead, you can use measure.

 

Date_Selected = CALCULATE(MAX('DATE'[Date]),ALLSELECTED('DATE'))

 

ReportFilter =
VAR selectedDate = 'Date'[Date_Selected]
VAR result =
    CALCULATE (
        IF (
            selectedDate > SELECTEDVALUE ( Inventory[Start] )
                && selectedDate <= SELECTEDVALUE ( Inventory[End] ),
            "Include",
            "Exclude"
        )
    )
RETURN
    result

 

snapshot

 

If required you apply a visual level filter.

 

Regards,

Nandu Krishna

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi nandukrishnavs , 

     

    Unfortantly this bough back the same results I got where it seems to not be applying the logic. 

     

     

    Thanks 

    Rob 

     

    • FrankAT's avatar
      FrankAT
      Community Champion
      6 years ago

      Hi Anonymous,

      are your Start and End date true calendar dates? If they are text it doesn't work!

      Regards FrankAT

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi FrankAT 

         

        Yeah these are for sure Calender dates! 

         

        v-juanli-msft  I tried changing the date slicer but this returned the same result! also this shouldn't make a diffrence as the Dax is going off the [Date_Selected} Column not the acutal slicer... 

         

        Thanks 

        Rob

         

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

      Hi Anonymous 

      It seems your slicer set as "before",

      so the range you select is date 2017/10/1~2018/9/1

      For the first line 2018/8/1~2018/11/12, it is not include in the selected date range.

       

      Best Regards

      Maggie

      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.