Forum Discussion
Create Table from adjusted date range based on slicer
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.
Still an issue...
I created this demo. Here's a link to the .pbix and .xlsx files: https://www.dropbox.com/scl/fi/9pagceic1e3zuidfvrcbb/DonorTest.pbix?rlkey=gwa1a2utd48q65ngkndtl8sa8&st=y57rnndx&dl=0
https://www.dropbox.com/scl/fi/02q6hm6cl5uz3s5eynu10/demo.xlsx?rlkey=zm36rhesums6dme7c4j2edv74&st=min8gt6f&dl=0
As you can see in the image below, while the matrix accurately displays only the total for entries within the date range for both current and sply, the table attached to SPLY Transactions Table is showing ALL the records in the month from 2023 instead of cutting off at 1/15. Effectively ignoring the filter set in the calculated table. Why??
Also, I tried to create the measure you suggested, but at the "IF(" point, all I get prompted are measures and when I try to add the reference to 'FinancialTrans'[Date] I get an error.