cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Quinnie2017
Helper II
Helper II

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

 

Quinnie2017_0-1686933163518.png

 

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 =
1 ACCEPTED SOLUTION
Quinnie2017
Helper II
Helper II

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

View solution in original post

6 REPLIES 6
Quinnie2017
Helper II
Helper II

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
Super User
Super User

Hi @Quinnie2017 

VALUES ( 'Table'[Column] )

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

@Quinnie2017 

And VALUES is a table!

what is your ultimate goal perhaps I can help?

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

 

@Quinnie2017 

Then use VALUES ( 'Table'[Column] )

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors