Calculate filtered values from prior periods

[Anonymous]

I have a model with a calendar table that has pre-computed prior dates that I would like to report on.  Now I would like to compute, alongside the current period, a prior period composed of all records matching a give Prior date of the current record, but with all the same filtering of other columns.  Only the date should be from the prior period.  I'm having trouble figuring out how to do this.  Should I create a new table with a relationship to the Prior column of the Calendar, or should I create computed columns in the existing table that somehow selects all the rows matching the Prior column, or something else?

An example:

Thanks!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can refer to following steps to achieve your requirement.

Steps:

1. Create three tables as column fields and selection datasource.

Header = DATATABLE("Type",STRING,{{"Current"},{"Prior"}})

Property1 = VALUES(Table2[Property'])

Property2 = VALUES(Table2[Property2])

2. Write a measure to use row contents and slicer selection items to calculate corresponding value.

Dynamic =
VAR currType =
    SELECTEDVALUE ( 'Header'[Type] )
VAR currDate =
    MAX ( Table2[Date] )
VAR currPrior =
    MAX ( Table2[Prior] )
RETURN
    CALCULATE (
        SUM ( Table2[Value] ),
        FILTER (
            ALL ( Table2 ),
            [Date] = SWITCH ( currType, "Prior", currPrior, "Current", currDate, BLANK () )
                && [Property'] IN ALLSELECTED ( Property1[Property'] )
                && [Property2] IN ALLSELECTED ( Property2[Property2] )
        )
    )
        + 0

3. Create slicers with selector tables columns.
4. Create matrix visual with original table date column as row, Header table type column as column, measure as value.

Regards,

Xiaoxin Sheng