topic Re: Pass slicer date range to a variable in DAX Commands and Tips

Pass slicer date range to a variable

Quinnie2017 — Fri, 16 Jun 2023 16:35:23 GMT

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 created variables that create the min and mix date for each of the slicers.

Now I want to create a variable that has the date range of each slicer.

What I am expecting is:

VAR _periodBefore: 01/17/2023 - 03/20/2023

VAR _periodAfter: 03/28/2023 - 05/29/2023

Before_Day_Key table is here.

After_Day_Key table is here.

Can you help complete the 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 =

Re: Pass slicer date range to a variable

tamerj1 — Fri, 16 Jun 2023 17:05:36 GMT

Hi Quinnie2017

VALUES ( 'Table'[Column] )

Re: Pass slicer date range to a variable

Quinnie2017 — Fri, 16 Jun 2023 17:24:19 GMT

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

Re: Pass slicer date range to a variable

tamerj1 — Fri, 16 Jun 2023 18:03:07 GMT

Quinnie2017

And VALUES is a table!

what is your ultimate goal perhaps I can help?

Re: Pass slicer date range to a variable

Quinnie2017 — Fri, 16 Jun 2023 18:32:55 GMT

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))

Re: Pass slicer date range to a variable

tamerj1 — Fri, 16 Jun 2023 18:45:17 GMT

Quinnie2017

Then use VALUES ( 'Table'[Column] )

Re: Pass slicer date range to a variable

Quinnie2017 — Thu, 22 Jun 2023 15:05:31 GMT

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))