Forum Discussion

carnage66's avatar
carnage66
Frequent Visitor
4 years ago
Solved

Filter last 6 months

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's avatar
    Anonymous
    4 years ago

    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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

     

     

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Nikkkkkkkkk
      New Member
      2 years ago

      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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

    • bharteshm's avatar
      bharteshm
      Frequent Visitor
      2 years ago

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