Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Data table to display preceding data based on date slicer

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 🙂

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

     

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.