Forum Discussion
Create Table from adjusted date range based on slicer
- 1 year ago
You don't need to duplicate the Transactions table manually instead you can use a DAX calculated table or a measure that feeds into your table visual.SPLY Transactions Table = VAR __minDate = MIN('Date'[Date]) VAR __maxDate = MAX('Date'[Date]) VAR __startDate = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate)) VAR __endDate = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate)) RETURN FILTER ( ADDCOLUMNS ( 'FinancialTrans', "TransactionDate", RELATED('Date'[Date]) ), [TransactionDate] >= __startDate && [TransactionDate] <= __endDate )If Customer is in your FinancialTrans table or related to it, then clicking a customer in the matrix will contextually filter this table too.
Or you can use a measure :
Show SPLY Txn = VAR __minDate = MIN('Date'[Date]) VAR __maxDate = MAX('Date'[Date]) VAR __startDate = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate)) VAR __endDate = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate)) RETURN IF ( 'FinancialTrans'[Date] >= __startDate && 'FinancialTrans'[Date] <= __endDate, 1, 0 )Then use a visual-level filter on the table to show only rows where Show SPLY Txn = 1.
You don't need to duplicate the Transactions table manually instead you can use a DAX calculated table or a measure that feeds into your table visual.
SPLY Transactions Table =
VAR __minDate = MIN('Date'[Date])
VAR __maxDate = MAX('Date'[Date])
VAR __startDate = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate))
VAR __endDate = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate))
RETURN
FILTER (
ADDCOLUMNS (
'FinancialTrans',
"TransactionDate", RELATED('Date'[Date])
),
[TransactionDate] >= __startDate &&
[TransactionDate] <= __endDate
)
If Customer is in your FinancialTrans table or related to it, then clicking a customer in the matrix will contextually filter this table too.
Or you can use a measure :
Show SPLY Txn =
VAR __minDate = MIN('Date'[Date])
VAR __maxDate = MAX('Date'[Date])
VAR __startDate = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate))
VAR __endDate = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate))
RETURN
IF (
'FinancialTrans'[Date] >= __startDate &&
'FinancialTrans'[Date] <= __endDate,
1,
0
)
Then use a visual-level filter on the table to show only rows where Show SPLY Txn = 1.
Amira,
Perfect! Thanks!
All I had to do was add another column: