Skip to main content
cancel
Showing results for 
Search instead 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

Reply
PowerBIWhat
Helper I
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 IDPeriodShift PeriodFlag Notice received
10001100

10002

100
10003101

10001

2-11
100022-11
100032-10

10001

3-2 0
100023-2 0
100033-2 0

10001

4-3 0
100024-3 0
100034-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,
1 ACCEPTED SOLUTION
PowerBIWhat
Helper I
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
    )
)

View solution in original post

2 REPLIES 2
PowerBIWhat
Helper I
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
    )
)
v-shex-msft
Community Support
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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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