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.

danextian's avatar
danextian
Super User
1 year ago

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.