Forum Discussion
Create calculated table using MAX and MIN date rows from report filter context (date range)
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
FilteredTable
Also 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
FilteredTable
This returns all rows in the report's filter range, not just the first and last (same result as xMV).