Last 5 months datat from a selected month filter.

[siva54]

Hi Team,

I need to a graph like, if we selected a month then we need to show the last 5 months in graph.(Seen in below)

example:- if we select year =2022 and Month = february then in graph we need to show  oct 2021, Nov 2021, Dec 2021, Jan 2022 and Feb 2022.

The data we have is 

Date	Values
January 01 2021	12
February 01 2021	33
March 01 2021	37
April 01 2021	64
May 01 2021	3
June 01 2021	88
July 01 2021	75
August 01 2021	31
September 01 2021	9
October 01 2021	8
November 01 2021	44
December 01 2021	18
January 01 2022	75
February 01 2022	44
March 01 2022	22
April 01 2022	68
May 01 2022	92
June 01 2022	37
July 01 2022	69
August 01 2022	90
September 01 2022	7
October 01 2022	60
November 01 2022	21
December 01 2022	12

Thanks in Advance

[v-yanjiang-msft · Accepted solution]

Hi @siva54 ,

According to your description, here's my solution.

1.Create a new date table,

Date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthNum", MONTH ( [Date] )
)

2.Create a measure,

Check =
IF (
    MAX ( 'Table'[Date] )
        > EOMONTH (
            DATE ( SELECTEDVALUE ( 'Date'[Year] ), SELECTEDVALUE ( 'Date'[MonthNum] ), 1 ),
            -5
        )
        && MAX ( 'Table'[Date] )
            <= EOMONTH (
                DATE ( SELECTEDVALUE ( 'Date'[Year] ), SELECTEDVALUE ( 'Date'[MonthNum] ), 1 ),
                0
            ),
    1,
    0
)

Put the new date table in the slicer, put the check measure in the visual filter and make its value to 1, get the correct result. 

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@siva54 , if you select one date/month and need more, you need slicer on independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -5) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI