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

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    try this?

    Report Filter = 

var _selectedDate = CALCULATE(max('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

var IncExc = if( _selectedDate > calculate(firstdate('Table'[from]),'Table') 
              && _selectedDate <= calculate(firstdate('Table'[to]),'Table'),
            "Include",
            "Exclude"
)

return IncExc

     

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

    Hi Anonymous 

    I don't know what causes your problem.

    On my side, if i have tables without relationships, then create measures

    add "date" from "calendar" table to the slicer,

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

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

    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.