Forum Discussion

phishneslo's avatar
phishneslo
Frequent Visitor
3 years ago

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

  • phishneslo's avatar
    phishneslo
    Frequent 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
    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).