Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors