Forum Discussion

Afkkek's avatar
Afkkek
Frequent Visitor
5 years ago
Solved

Looking up a date value N Observations back

Hello,

 

Was wondering if anyone had any advice for how one can look up a specific value from a column (let's say a date), by going back N number of rows from a selected maximum date.

 

So in the example below, if let's say a slicer has Feb 28, 2000 selected, then for each category, the 5th date/row prior to Feb 28th would be selected: so 1/31/2000 for Category A, 1/10/2000 for Category B and...nothing for Category C because all the observations are after the slicer date (ignore the highlighting of 1/3/2001 that was a mistake when I mocked up the example).

 

Thanks in advance

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Assume you have a table named "DateTest" as follows...

    And a calculated table named "DateCal" using the code for the independent date slicer. (No relationship between these two tables)

    DateCal = CALENDAR(DATE(2000,1,1),DATE(2001,12,1))

    And use the DateCal[Date] field on a slicer and select the date "28th Feb 2000" on the slicer.

    The following measure will give you the result that you are expecting...

    SelectedDate =
VAR SlicerDate =
    SELECTEDVALUE ( DateCal[Date] )
VAR CurrentCategory =
    SELECTEDVALUE ( DateTest[Category] )
VAR CurrentDate =
    SELECTEDVALUE ( DateTest[Date] )
VAR FilteredTable =
    FILTER (
        ALLSELECTED ( DateTest ),
        DateTest[Category] = CurrentCategory
            && DateTest[Date] <= SlicerDate
    )
VAR AddDateDifference =
    ADDCOLUMNS ( FilteredTable, "DateDifference", SlicerDate - [Date] )
VAR AddDateRank =
    ADDCOLUMNS (
        AddDateDifference,
        "DateRank", RANKX ( AddDateDifference, [DateDifference], [DateDifference], ASC, DENSE )
    )
VAR FilterADR =
    FILTER ( AddDateRank, [DateRank] = 5 && [Date] = CurrentDate )
VAR SelectDate =
    IF ( COUNTROWS ( FilterADR ) > 0, CurrentDate, BLANK () )
RETURN
    SelectDate

     

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Afkkek ,

    First, please create a Date table and create a slicer base on the date file of this table. Then create a measure as below to get the related date:

    Measure =
VAR _seldate = SELECTEDVALUE ( 'Date'[Date] )
VAR _selcategory = SELECTEDVALUE ( 'Table'[Category] )
VAR _tab =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Date] <= _seldate ),
        'Table'[Category],
        'Table'[Date],
        "vRank",
            RANKX (
                FILTER ( ALL ( 'Table' ), 'Table'[Category] = _selcategory ),
                CALCULATE ( MAX ( 'Table'[Date] ) ),
                ,
                ASC,
                DENSE
            )
    )
VAR _tab2 =
    ADDCOLUMNS (
        _tab,
        "count",
            COUNTROWS (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Category] = _selcategory
                        && 'Table'[Date] <= _seldate
                )
            )
    )
RETURN
    MAXX ( FILTER ( _tab2, [vRank] = [count] - 4 ), [Date] )

    Best Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Assume you have a table named "DateTest" as follows...

    And a calculated table named "DateCal" using the code for the independent date slicer. (No relationship between these two tables)

    DateCal = CALENDAR(DATE(2000,1,1),DATE(2001,12,1))

    And use the DateCal[Date] field on a slicer and select the date "28th Feb 2000" on the slicer.

    The following measure will give you the result that you are expecting...

    SelectedDate =
VAR SlicerDate =
    SELECTEDVALUE ( DateCal[Date] )
VAR CurrentCategory =
    SELECTEDVALUE ( DateTest[Category] )
VAR CurrentDate =
    SELECTEDVALUE ( DateTest[Date] )
VAR FilteredTable =
    FILTER (
        ALLSELECTED ( DateTest ),
        DateTest[Category] = CurrentCategory
            && DateTest[Date] <= SlicerDate
    )
VAR AddDateDifference =
    ADDCOLUMNS ( FilteredTable, "DateDifference", SlicerDate - [Date] )
VAR AddDateRank =
    ADDCOLUMNS (
        AddDateDifference,
        "DateRank", RANKX ( AddDateDifference, [DateDifference], [DateDifference], ASC, DENSE )
    )
VAR FilterADR =
    FILTER ( AddDateRank, [DateRank] = 5 && [Date] = CurrentDate )
VAR SelectDate =
    IF ( COUNTROWS ( FilterADR ) > 0, CurrentDate, BLANK () )
RETURN
    SelectDate

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Afkkek ,

    First, please create a Date table and create a slicer base on the date file of this table. Then create a measure as below to get the related date:

    Measure =
VAR _seldate = SELECTEDVALUE ( 'Date'[Date] )
VAR _selcategory = SELECTEDVALUE ( 'Table'[Category] )
VAR _tab =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Date] <= _seldate ),
        'Table'[Category],
        'Table'[Date],
        "vRank",
            RANKX (
                FILTER ( ALL ( 'Table' ), 'Table'[Category] = _selcategory ),
                CALCULATE ( MAX ( 'Table'[Date] ) ),
                ,
                ASC,
                DENSE
            )
    )
VAR _tab2 =
    ADDCOLUMNS (
        _tab,
        "count",
            COUNTROWS (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Category] = _selcategory
                        && 'Table'[Date] <= _seldate
                )
            )
    )
RETURN
    MAXX ( FILTER ( _tab2, [vRank] = [count] - 4 ), [Date] )

    Best Regards