Forum Discussion
Create calculated table using MAX and MIN date rows from report filter context (date range)
I am pretty new at PBI and DAX...
The earliest date in the extHoldings table is 12/31/04 and the last date in the extHoldings table is 6/30/23. Prior to relating the columns from xMVbe to my dimension tables, the following code returned the rows corresponding to the MAX and MIN dates of the data table:
xMVbe =
VAR FilteredTable =
SELECTCOLUMNS (
CALCULATETABLE (
extHoldings,
extHoldings[Rep Date] = MAX ( extHoldings[Rep Date] )
|| extHoldings[Rep Date] = MIN ( extHoldings[Rep Date] )
),
"Port Code", extHoldings[Port Code],
"Symbol", extHoldings[Symbol],
"Date", extHoldings[Rep Date],
"Flow", extHoldings[M Value]
)
RETURN
FilteredTable
This returns the first and last date rows from the extHoldings table. I would like to use the date range from the report's filter context using the date range filter in the report, not the entire range of the data table.
For example, I want to display the rows for 12/31/22 and 3/31/23, which are the first and last dates in the report's date filter.
When the report's date filter is applied, the xMVbe table is empty. I assume this is because xMVbe (prior to the filter) contains the MIN and MAX rows from the data table (12/31/04 and 6/30/23) and there is nothingin the range of the report date filter, but I cant figure out how to get the calculated table to use the report filter date range as the MAX and MIN dates.
I do have a dimDate table, and the Date column is being used by the report filter.
Maybe I am going about this the wrong way, but I imagined using selectcolumns to pick columns from the extHoldings table, and calculatetable to filter all rows but the min and max dates, and putting this in the xMVbe calculated table.
I need this range to be based on the report date range filter, not on the max and min date of the extHoldings table itself.
(This is all to support a union of the xMVbe table and a cashflows table that contains flows between the MAX and MIN dates, as part of an XIRR measure)
First time poster, long time lurker... Sorry if I didn't explain clearly... I can add more info or answer any questions. Thanks in advance for any help!
P
1 Reply
- phishnesloFrequent Visitor
Also tried (same results, nothing appears in table visual):
xMVbe =
VAR FilteredTable =
SELECTCOLUMNS (
CALCULATETABLE (
extHoldings,
FILTER (
ALLSELECTED ( extHoldings ),
extHoldings[Rep Date] = MAX ( extHoldings[Rep Date] )
|| extHoldings[Rep Date] = MIN ( extHoldings[Rep Date] )
)
),
"Port Code", extHoldings[Port Code],
"Symbol", extHoldings[Symbol],
"Date", extHoldings[Rep Date],
"Flow", extHoldings[M Value]
)
RETURN
FilteredTableAlso tried (same results, nothing appears in table visual):
xMVbe =
VAR FilteredTable =
SELECTCOLUMNS (
FILTER (
extHoldings,
extHoldings[Rep Date] = CALCULATE ( MIN ( extHoldings[Rep Date] ), ALLSELECTED ( extHoldings ) )
|| extHoldings[Rep Date] = CALCULATE ( MAX ( extHoldings[Rep Date] ), ALLSELECTED ( extHoldings ) )
),
"Port Code", extHoldings[Port Code],
"Symbol", extHoldings[Symbol],
"Date", extHoldings[Rep Date],
"Flow", extHoldings[M Value]
)
RETURN
FilteredTable
The following works as expected:xMV =
SELECTCOLUMNS(
extHoldings,
"Port Code", extHoldings[Port Code],
"Symbol", extHoldings[Symbol],
"Date", extHoldings[Rep Date],
"Flow", extHoldings[M Value]
)when I set the report date filter range to 12/31/22 - 3/31/23, and use the columns in a table visulization, this gives me a table visual that contains 12/31/2022, 1/31/23, 2/28/23, and 3/31/23.
I want to do something similar with a new calculated table named "xMVbe" that contains only the first and last value of the filtered range (12/31/22 and 3/31/23)Also tried:
xMVbe =
VAR FilteredTable =
SELECTCOLUMNS (
FILTER (
extHoldings,
extHoldings[Rep Date] = FIRSTDATE ( extHoldings[Rep Date] )
|| extHoldings[Rep Date] = LASTDATE ( extHoldings[Rep Date] )
),
"Port Code", extHoldings[Port Code],
"Symbol", extHoldings[Symbol],
"Date", extHoldings[Rep Date],
"Flow", extHoldings[M Value]
)
RETURN
FilteredTableThis returns all rows in the report's filter range, not just the first and last (same result as xMV).