Forum Discussion

DianaZ's avatar
DianaZ
Regular Visitor
4 years ago
Solved

How to display only selected date value in Power bi

 

I need help with my formula and visualization. Above is the set of data and slicer that has been set in my dashboard.

What I would like to do is

a) if I select 2022-02 (feb 2022) reporting period in slicer, the visual should only show trend data of 2020-12, 2021-12 and 2022-02.

b) if I select 2022-01 (jan 2022) period in slicer, the visual should only show trend 2020-12, 2021-12 and 2022-01.

 

Below is my trending formula 

SBO FTE Trending = CALCULATE('01 Measures'[SBO FTE], REMOVEFILTERS('Filter Reporting Period'[Reporting Period]))
 
My formula end up showing all values in my data. Can anyone help me how can should I adjust the formula so that it picks up 2 previous years and latestmonth (based on slicer selection from slicer) values only?
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  DianaZ ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

    SBO FTE Trending = 
VAR _selperiod =
    SELECTEDVALUE ( 'Filter Reporting Period'[Reporting Period] )
VAR _numperiod =
    VALUE ( LEFT ( _selperiod, 4 ) & RIGHT ( _selperiod, 2 ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Trend] ),
        FILTER (
            'Table',
            VALUE ( YEAR ( 'Table'[Date] ) & FORMAT ( 'Table'[Date], "mm" ) ) <= _numperiod
        )
    )

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

    Best Regards

2 Replies

  • arvindsingh802's avatar
    arvindsingh802
    Community Champion
    4 years ago

    You can use SBO FTE Trending =
    _Var sv = SELECTEDVALUE('Filter Reporting Period'[Reporting Period])
    RETURN
    CALCULATE('01 Measures'[SBO FTE], REMOVEFILTERS('Filter Reporting Period'[Reporting Period]), 'Filter Reporting Period'[Reporting Period] = sv || 'Filter Reporting Period'[Reporting Period] = "2020-12", || 'Filter Reporting Period'[Reporting Period] = "2021-12")

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  DianaZ ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

    SBO FTE Trending = 
VAR _selperiod =
    SELECTEDVALUE ( 'Filter Reporting Period'[Reporting Period] )
VAR _numperiod =
    VALUE ( LEFT ( _selperiod, 4 ) & RIGHT ( _selperiod, 2 ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Trend] ),
        FILTER (
            'Table',
            VALUE ( YEAR ( 'Table'[Date] ) & FORMAT ( 'Table'[Date], "mm" ) ) <= _numperiod
        )
    )

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

    Best Regards