cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

moth change

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

1 ACCEPTED SOLUTION

Accepted Solutions
2 REPLIES 2
Highlighted
Microsoft
Microsoft

Hi @greeshma ,

 

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.

1.png

 

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"
)
)

2.png

 

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
                                            )
                                    )
                                )
                        )
                )
        )
    )

result9291109.gif

 

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors