Use measure to show value from period before selected value in slicer

[PowerBIWhat]

Hi,

I have a dataset sample which looks like the following.

Unit ID	Period	Shift Period	Flag Notice received
10001	1	0	0
10002	1	0	0
10003	1	0	1
10001	2	-1	1
10002	2	-1	1
10003	2	-1	0
10001	3	-2	0
10002	3	-2	0
10003	3	-2	0
10001	4	-3	0
10002	4	-3	0
10003	4	-3	0

I have a visual which is a table which is currently filtered on shift period 0, so that it always displays the current period. I want to add a column to that table which shows a sum of Notices Received but for shift period - 1. So the visual displays a comparison of the period and the prior period. 

I need the measure to be dynamic as in if somebody changes the filter on the table to be shift period -1, then I want the measure to sum notices received for period -2.

The measure I've used is below and displays the correct result when I add it to a separate visual, but when I filter that visual with shift period. It all goes wrong and I get a blank. So it won't work in my table.

3 - Flag on Notice at period end (Prior Period) =

VAR SelectedShiftPeriod = SELECTEDVALUE('table 1'[Shift period], 0) -1

VAR Result =

CALCULATE(

    SUM('table 1'[Flag Notice received]),

    'table 1'[Shift period] = SelectedShiftPeriod

)

RETURN

Result

Hope that's clear.

Thanks,

[PowerBIWhat · Accepted solution]

I solved this by creating a calculated column.

Previous Flag on Notice at period end =

VAR CurrentPeriod = 'table1'[Shift period]

VAR CurrentIdUnit = 'table1'[Unit Id]

RETURN

CALCULATE(

    MAX('table1'[Flag on Notice at period end]),

    FILTER(

        ALL('table1'),

          'table1'[Unit Id] = CurrentIdUnit

            && 'table1'[Shift period] = CurrentPeriod - 1

    )

)

[Anonymous]

HI @PowerBIWhat ,

AFAIK, current power bi does not support create dynamic calculated column/table based on filter selections. They not work on the same level and you can't use child level to affect its parent.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng