cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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(
'table 1'[Shift period] = SelectedShiftPeriod
)
RETURN
Result

Hope that's clear.

Thanks,
1 ACCEPTED SOLUTION
Helper I

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
)
)
2 REPLIES 2
Helper I

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
)
)
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors