moth change

[Anonymous]

hiii
i want a line graph which needs to display past 2 months data and present month data based on the slicer selection
like if i select april my line graph should display feb march and april data
of raw sla% , m.sla% and call closed
how can i do that can some one show that

https://drive.google.com/file/d/1lTcK5CQsdl3rteKc5GLimkEmjcjPDGR7/view?usp=sharing

[v-stephen-msft]

Hi @Anonymous ,

1.If you want to display past 2 months data, you need to make sure that the filter values in your slicer are not related to the sample data.

2.Create YearMonth column for the slicer.

Table = 
ADDCOLUMNS (
    FILTER ( VALUES ( Query1[Call Closed] ), [Call Closed] <> BLANK () ),
    "Year", YEAR ( [Call Closed] ),
    "Month", MONTH ( [Call Closed] ),
    "YearMonth",YEAR([Call Closed])&"-"&FORMAT([Call Closed],"MM"
)
)

3.Two measures are these.

M.SLA% = 
VAR _sel =
    SELECTEDVALUE ( 'Table'[YearMonth] )
RETURN
    CALCULATE (
        IFERROR ( ( Query1[M HIT] / Query1[M.COUNT] * 100 ), 100 ),
        FILTER (
            'Query1',
             (
                 (
                    MONTH ( 'Query1'[Call Closed] ) = VALUE ( RIGHT ( _sel, 2 ) )
                        || MONTH ( 'Query1'[Call Closed] )
                            = VALUE ( RIGHT ( _sel, 2 ) ) - 1
                        || MONTH ( 'Query1'[Call Closed] )
                            = VALUE ( RIGHT ( _sel, 2 ) ) - 2
                )
                    && YEAR ( 'Query1'[Call Closed] ) = VALUE ( LEFT ( _sel, 4 ) )
                    && VALUE ( RIGHT ( _sel, 2 ) ) IN { 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 }
            )
                || (
                    VALUE ( RIGHT ( _sel, 2 ) ) = 2
                        && (
                             (
                                YEAR ( 'Query1'[Call Closed] ) = VALUE ( LEFT ( _sel, 4 ) )
                                    && (
                                        MONTH ( 'Query1'[Call Closed] ) = VALUE ( RIGHT ( _sel, 2 ) )
                                            || MONTH ( 'Query1'[Call Closed] )
                                                = VALUE ( RIGHT ( _sel, 2 ) ) - 1
                                    )
                            )
                                || (
                                     (
                                        YEAR ( 'Query1'[Call Closed] )
                                            = VALUE ( LEFT ( _sel, 4 ) ) - 1
                                            && ( MONTH ( 'Query1'[Call Closed] ) = 12 )
                                    )
                                )
                        )
                )
                || (
                    VALUE ( RIGHT ( _sel, 2 ) ) = 1
                        && (
                             (
                                YEAR ( 'Query1'[Call Closed] ) = VALUE ( LEFT ( _sel, 4 ) )
                                    && ( MONTH ( 'Query1'[Call Closed] ) = VALUE ( RIGHT ( _sel, 2 ) ) )
                            )
                                || (
                                     (
                                        YEAR ( 'Query1'[Call Closed] )
                                            = VALUE ( LEFT ( _sel, 4 ) ) - 1
                                            && (
                                                MONTH ( 'Query1'[Call Closed] ) = 12
                                                    || MONTH ( 'Query1'[Call Closed] ) = 11
                                            )
                                    )
                                )
                        )
                )
        )
    )

RAW SLA% = 
VAR _sel =
    SELECTEDVALUE ( 'Table'[YearMonth] )
RETURN
    CALCULATE (
        IFERROR ( ( Query1[RAW HIT] / Query1[RAW COUNT] * 100 ), 100 ),
        FILTER (
            'Query1',
             (
                 (
                    MONTH ( 'Query1'[Call Closed] ) = VALUE ( RIGHT ( _sel, 2 ) )
                        || MONTH ( 'Query1'[Call Closed] )
                            = VALUE ( RIGHT ( _sel, 2 ) ) - 1
                        || MONTH ( 'Query1'[Call Closed] )
                            = VALUE ( RIGHT ( _sel, 2 ) ) - 2
                )
                    && YEAR ( 'Query1'[Call Closed] ) = VALUE ( LEFT ( _sel, 4 ) )
                    && VALUE ( RIGHT ( _sel, 2 ) ) IN { 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 }
            )
                || (
                    VALUE ( RIGHT ( _sel, 2 ) ) = 2
                        && (
                             (
                                YEAR ( 'Query1'[Call Closed] ) = VALUE ( LEFT ( _sel, 4 ) )
                                    && (
                                        MONTH ( 'Query1'[Call Closed] ) = VALUE ( RIGHT ( _sel, 2 ) )
                                            || MONTH ( 'Query1'[Call Closed] )
                                                = VALUE ( RIGHT ( _sel, 2 ) ) - 1
                                    )
                            )
                                || (
                                     (
                                        YEAR ( 'Query1'[Call Closed] )
                                            = VALUE ( LEFT ( _sel, 4 ) ) - 1
                                            && ( MONTH ( 'Query1'[Call Closed] ) = 12 )
                                    )
                                )
                        )
                )
                || (
                    VALUE ( RIGHT ( _sel, 2 ) ) = 1
                        && (
                             (
                                YEAR ( 'Query1'[Call Closed] ) = VALUE ( LEFT ( _sel, 4 ) )
                                    && ( MONTH ( 'Query1'[Call Closed] ) = VALUE ( RIGHT ( _sel, 2 ) ) )
                            )
                                || (
                                     (
                                        YEAR ( 'Query1'[Call Closed] )
                                            = VALUE ( LEFT ( _sel, 4 ) ) - 1
                                            && (
                                                MONTH ( 'Query1'[Call Closed] ) = 12
                                                    || MONTH ( 'Query1'[Call Closed] ) = 11
                                            )
                                    )
                                )
                        )
                )
        )
    )

You can download this to check.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007 · Accepted solution]

@greeshma I couldn't open the file as I have the previous version of Power Bi. But you can check the links below for your problem

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

https://stackoverflow.com/questions/49146565/show-last-3-months-from-selected-month-in-power-bi

https://radacad.com/only-get-the-last-few-periods-of-data-into-power-bi-using-power-query-filtering