Filter last 6 months

[carnage66]

I want to filter the last 6 months, so today in December, it should show Jul, Aug, Sep, Oct, Nov, Dec.

However, if I use date filter "in the last 6 months", it starts from Jun and cuts days with the days that have passed in December.

How do I make a filter that will start from 1st of Jul and dynamically change every next month to the 1st of Aug, then Sep, etc.

[Anonymous]

Hey, sorry for the late reply , there is simple way to get the last 6 months data with or without current month ,and we don't need to write any DAX measure for this. we can do the changes in the PBI Desktop only. 

first select the table then go on page level filter -->drag your calendar column --> refer below

by choosing months it will dispaly until current month

by choosing calender month it will display the completed months

like this you can choose for years also

if it helped please save the solution and ACCEPT IT AS SOLUTION please

[v-eqin-msft · Accepted solution]

Hi @carnage66 ,

I have built a Calendar table by:

Table = CALENDAR(DATE(2020,9,1),TODAY())

Now please try to create a flag measure:

Flag = IF(MAX('Table'[Date])> EOMONTH(MAXX(ALL('Table'),[Date]),-6),1,0)

And apply it to filter pane:

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

[bharteshm]

It working for table, but not on matrix if I have Date as Columns. Any solution

[Anonymous]

@v-eqin-msft : Here i have the fiscal month column containing the data like FY22JAN , i need to use fiscal month column only to show the months in table visual, and the fiscal month column data type is in text format. in this case how can i acheive to display last six months except the current month. please help me to resolve this. i need the solution immediately as soon as possible. 

Note: The date table is not in the hirarchey format.

[amitchandak]

@carnage66 , Last 6 month of data based on selected date

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

Based on today

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today(),-6,MONTH))

you can switch based on isfiltered

But if you need trend based on selected date you need independent table

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

[Nikkkkkkkkk]

Hi, 

Could you please tell is it posssible to show sum of last 6 months if in the table I chose only 1 year and month, to avoid of "based on selected date". 

Thanks!