Forum Discussion

Quinnie2017's avatar
Quinnie2017
Helper II
3 years ago
Solved

Pass slicer date range to a variable

I am having trouble passing a slicer date range to a variable.   I have two tables: 1.  Before_Day_Key is used for my before slicer 2.  After_Day_Key is used for my after slicer   I have create...
Between dates
slicers
variable
  • Quinnie2017's avatar
    Quinnie2017
    3 years ago

    I believe I was in error in creating two new date tables.  Instead within my original date table, I created two date columns and referenced those columns in my DAX.  Ultimately using VALUES as tamerj1 suggested worked.  However I was unable to place all my VAR in one table because the table can only react to one slicer.  Ugh.

     

    PED2 =
    VAR _minDateBefore = MIN('Day Key'[BeforeSlicer])
    VAR _maxDateBefore = MAX('Day Key'[BeforeSlicer])
    VAR _minDateAfter = MIN('Day Key'[AfterSlicer])
    VAR _maxDateAfter = MAX('Day Key'[AfterSlicer])
    VAR _periodBefore = VALUES('Day Key'[BeforeSlicer])
    VAR _periodAfter = VALUES('Day Key'[AfterSlicer])
    VAR _beforeUPT = CALCULATE([UPT],_periodBefore)
    VAR _afterUPT = CALCULATE([UPT],_periodAfter)
    VAR _UPTchange = CALCULATE(DIVIDE(_afterUPT-_beforeUPT,_beforeUPT))
    VAR _beforePrice = CALCULATE(AVERAGE([Menu Price]),_periodBefore)
    VAR _afterPrice = CALCULATE(AVERAGE([Menu Price]),_periodAfter)
    VAR _PriceChange = CALCULATE(DIVIDE(_afterPrice-_beforePrice,_beforePrice))
    VAR _beforeMIX = CALCULATE([%MenuMixItem],_periodBefore)
    VAR _afterMIX = CALCULATE([%MenuMixItem],_periodAfter)

    RETURN
    CALCULATE(DIVIDE(_UPTchange,_PriceChange))
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Quinnie2017 

VALUES ( 'Table'[Column] )

Quinnie2017's avatar
Quinnie2017
Helper II
3 years ago

Thanks but I cannot get that to work because the slicer is a date range, any other ideas?

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Quinnie2017 

    And VALUES is a table!

    what is your ultimate goal perhaps I can help?

    • Quinnie2017's avatar
      Quinnie2017
      Helper II
      3 years ago

      Ultimate goal is to complete the following statement:

       

      PED2 =
      VAR _minDateBefore = MIN('Before_Day_Key'[Business Date])
      VAR _maxDateBefore = MAX('Before_Day_Key'[Business Date])
      VAR _minDateAfter = MIN('After_Day_Key'[Business Date])
      VAR _maxDateAfter = MAX('After_Day_Key'[Business Date])
      VAR _periodBefore =
      VAR _periodAfter =
      VAR _beforeUPT = CALCULATE([UPT],_periodBefore)
      VAR _afterUPT = CALCULATE([UPT],_periodAfter)
      VAR _UPTchange = CALCULATE(DIVIDE(_afterUPT-_beforeUPT,_beforeUPT))
      VAR _beforePrice = CALCULATE(AVERAGE([Menu Price]),_periodBefore)
      VAR _afterPrice = CALCULATE(AVERAGE([Menu Price]),_periodAfter)
      VAR _PriceChange = CALCULATE(DIVIDE(_afterPrice-_beforePrice,_beforePrice))
      VAR _beforeMIX = CALCULATE([%MenuMixItem],_periodBefore)
      VAR _afterMIX = CALCULATE([%MenuMixItem],_periodAfter)

      RETURN
      CALCULATE(DIVIDE(_UPTchange,_PriceChange))

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Quinnie2017 

        Then use VALUES ( 'Table'[Column] )