Forum Discussion
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
@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
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
2 Replies
- negi007
Community Champion
@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
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
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.