Forum Discussion

newhopepdx's avatar
newhopepdx
Resolver I
1 year ago
Solved

Create Table from adjusted date range based on slicer

Have a matrix with: Customer, the # of transactions previous period, # transaction in the slicer-based period. And a slicer that sets the start and ending dates. Am using the measure below to calcu...
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    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.

rohit1991's avatar
rohit1991
Super User
1 year ago

Hi newhopepdx ,

To dynamically display transaction details from the previous period based on a slicer-defined date range and a selected customer in your matrix, you can create a calculated table that adjusts to both the slicer and user interactions. The idea is to replicate the time-shifted logic you’ve used in your measure, but apply it to a table that filters the actual rows of transaction data.

 

By referencing the start and end dates from the slicer and shifting them back by one year, and filtering based on the selected customer from the matrix, the calculated table will only return relevant transaction records for that customer in the corresponding previous period. This approach allows you to use the table visual effectively without needing to duplicate your entire transaction table manually.

 

TransactionsSPLYDetails =
VAR __minDate = MIN('Date'[Date])
VAR __maxDate = MAX('Date'[Date])
VAR __startDt = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate))
VAR __endDt = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate))
VAR __selectedCustomer = SELECTEDVALUE('Customer'[CustomerName]) -- Adjust field name if needed

RETURN
CALCULATETABLE(
    'FinancialTrans',
    'FinancialTrans'[CustomerName] = __selectedCustomer,
    'Date'[Date] >= __startDt,
    'Date'[Date] <= __endDt
)