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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors