Create Table from adjusted date range based on slicer

[newhopepdx]

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 calculate the previous period data.

Wanting to also (when clicking on a specific Customer in the matrix) fill a table visual with the transactions that made up the rows counted by the measure for that customer.

Seems like I could duplicate the Transaction table and set a filter on it, then use it as the basis for the table visual. But how do you create such a table dynamically?

Transactions SPLY = 

VAR __maxDate = MAX('Date'[Date])

VAR __minDate = MIN('Date'[Date])

VAR __startDt = DATE( YEAR( __minDate)-1, MONTH(__minDate), DAY(__minDate))

VAR __endDt = DATE( YEAR(__maxDate)-1, MONTH(__maxDate), DAY( __maxDate))

VAR __return = CALCULATE(

       COUNTROWS('FinancialTrans'),

       'Date'[Date] >= __startDt && 'Date'[Date] <= __endDt

)

RETURN

__return

[danextian]

Hi @newhopepdx 

A basic SAMEPERIODLASTYEAR time intelligence calculation on a dates table would work if the goal were only to compute the previous year's value. In that case, a related dates table would be sufficient. However, this method only displays rows within the selected date range, even though the calculation itself still retrieves the previous year's data. To overcome this limitation and display transactions contributing to the previous year's total, a disconnected table is required - which is a table that is not directly related to the model’s fact tables through relationships. Instead, it is used as an independent reference, often for calculations, parameter selections, or alternative filtering logic.

You can see in the image below that although the selected dates are from 2024, transaction dates from the prior year are still visible.

The measures used are:

Sum of Amount (Disconnected) = 
CALCULATE (
    [Sum of Amount],
    KEEPFILTERS (
        TREATAS ( VALUES ( DisconnectedCalendar[Date] ), CalendarTable[Date] )
    )
)

Previous Year Sum of Amount (Disconnected) =
CALCULATE (
    [Sum of Amount],
    KEEPFILTERS (
        TREATAS (
            SELECTCOLUMNS (
                DisconnectedCalendar,
                "Date", DATEADD ( DisconnectedCalendar[Date], -1, YEAR )
            ),
            CalendarTable[Date]
        )
    )
)

Please see the attached sample pbix.

[rohit1991]

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
)

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar

[AmiraBedh · Accepted solution]

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.

[newhopepdx]

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&...

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. 

[newhopepdx]

Amira,

Perfect! Thanks!

All I had to do was add another column: 

"AcntName", RELATED(Accounts[Account])  // added the Account Type for the transaction

and I had everything I needed.

Apprecitate the help.