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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.