Data table to display preceding data based on date slicer

[Anonymous]

Hi Community,

I have a data set that contains transaction records based on a certain transaction dates. These transactions are reported as of a quarter end date. 

Visualization: Data table

Based on my filter date selection, I would like my table to show all past transactions, up to my quarter end date.

For ex, I have 5 transaction as of date 2020 Q1 and 2 transactions as of date 2020 Q2.

When I filter on date 2020 Q1, table shows 5 transactions

When I filter on date 2020 Q2, the table should display 7 transactions (5 of prior quarter and 2 current)

Attaching a copy of the Sample PBIX 

Thanks,

Brian

@AlexisOlson, Does this fall in your forte? Thanks 🙂

[v-xiaotang · Accepted solution]

Hi @Anonymous 

1.  it's great that you have the same column in two tables,  

2. then keep the 2 tables disconnected, and drag the column Quarter in Date table into slicer

3. create the filter measure by using Date column(type:date) and Quarter column(type:text) in your table Data, and drag it into the filter on this visual 

filterMeasure =
VAR _selQ =
    SELECTEDVALUE ( 'Date'[Quarter] )
VAR _selDate =
    CALCULATE (
        MAX ( Data[As Of Date] ),
        FILTER ( ALL ( Data ), Data[As of Date - Year Quarter] = _selQ )
    )
VAR _startDate =
    DATE ( YEAR ( _selDate ), 1, 1 )
RETURN
    IF (
        ISBLANK ( _selQ ),
        1,
        IF (
            MIN ( Data[As Of Date] ) >= _startDate
                && MIN ( Data[As Of Date] ) <= _selDate,
            1,
            0
        )
    )

result:

See sample file attached bellow.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

[Anonymous]

Thanks so much,

I incorporated the disconnected table and a simpler solution which is just check if date values are less than or equal to selected date then 1, else 0. Pulled this measure as a filter in the table and filtered to only 1's.