topic Filter last 6 months in DAX Commands and Tips

Filter last 6 months

carnage66 — Wed, 08 Dec 2021 15:16:38 GMT

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.

Re: Filter last 6 months

amitchandak — Wed, 08 Dec 2021 15:31:31 GMT

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

Re: Filter last 6 months

Anonymous — Mon, 13 Dec 2021 08:53:04 GMT

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.

Re: Filter last 6 months

Anonymous — Wed, 22 Jun 2022 16:38:43 GMT

Anonymous : 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.

Re: Filter last 6 months

Anonymous — Thu, 23 Jun 2022 14:41:51 GMT

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

Re: Filter last 6 months

Nikkkkkkkkk — Sun, 11 Feb 2024 15:53:37 GMT

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!

Re: Filter last 6 months

bharteshm — Fri, 07 Jun 2024 07:11:10 GMT

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