select the data of 6 recent months based on text date slicer

[Anonymous]

Hi, here is my data looks like:

filterYM is the slicer, I want to show 6 months based on selected filterYM, for example, when I select filterYM=202205, how can I get the data that dataYM start from 202112 and end with 202205, do you have any ideas? Thank you.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a YM dimension table

2. Create a measure as below:

Flag = 
VAR _selfym =
    SELECTEDVALUE ( 'FilterYM'[dataYM] )
VAR _selym =
    SELECTEDVALUE ( 'Table'[dataYM] )
VAR _year =
    VALUE ( LEFT ( _selfym, 4 ) )
VAR _month =
    VALUE ( RIGHT ( _selfym, 2 ) )
VAR _minym =
    IF (
        _month >= 6,
        VALUE (
            CONCATENATE ( _year, IF ( _month - 5 < 10, "0" & ( _month - 5 ), _month - 5 ) )
        ),
        VALUE (
            CONCATENATE (
                _year - 1,
                IF (
                    12 - ( 5 - _month ) < 10,
                    "0" & ( 12 - ( 5 - _month ) ),
                    12 - ( 5 - _month )
                )
            )
        )
    )
RETURN
    IF ( VALUE ( _selym ) >= _minym && VALUE ( _selym ) <= VALUE ( _selfym ), 1, 0 )

3. Create a visual with the filter condition(Flag is 1)

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. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@Anonymous , Create a table with distinct filterYM and dataYM , and then create rank on dataYM 

dataYM Rank = Rankx('Date',[dataYM],,asc, dense)

Use filterYM  in slicer from this new date table 

Then try a meausre 

measure =

Var _max = maxx(allselected('Date'), 'Date'[dataYM Rank])

return 

calculate(sum(Table[Value]), filter('Date', 'Date'[dataYM Rank] >= _max -6 && 'Date'[dataYM Rank] <=_max))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s