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:

 

  1. (Measure) Date_Selected = CALCULATE(MAX('DATE'[Date]),ALLSELECTED('DATE'))
  2. dwh InventoryTenancy'[Start] = Colum from data set
  3. dwh InventoryTenancy'[End] = Colum from data set

In the below screen shot the highlighed "Exclude" should be "Include". 

 

 

Any help is always appricated. 

Thanks 

Dobby Libr3 

 

 

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

13 Replies

  • 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

         

  • FrankAT's avatar
    FrankAT
    Community Champion
    6 years ago

    Hi,

    try the following measure:

    Report Filter = 
IF (
    MIN ( 'dwh InventoryTenancy'[Start] )
        > CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) &&
    MIN ( 'dwh InventoryTenancy'[End] )
        <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ),
    "Include",
    "Exclude"
)

    Regards FrankAT

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi FrankAT, 

       

      thanks for your input, I currently getting the insufficent memory error, ( I have 32gb RAM and am connecting to a datawarehouse) so I don't think its really a memory issue.  

       

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

    Hi Anonymous 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
    If not, please feel free to let me know.
     
    Best Regards
    Maggie
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Maggie, 

       

      Still not resolved, I have tired out all the Measures in the chat and also looked at the model, I can't figure out why its not picking up, Also my data set has less than 20,000 line and the refresh on visuals takes far too long for the simple calculations. 

       

      there is a picture of my model, date table is not connected... 

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        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