Forum Discussion

hkhan's avatar
hkhan
New Member
4 years ago
Solved

Prior Rolling 12 Months Filtering

Hi, I want to filter my data on the last 12 months period. For instance right now if I select year as 2022 and month as Feb, I want the data to be filtered on the last 12 months such as from Feb 2021 till Feb 2022. I've been trying a lot of solutions but nothing worked out unfortunately. Would appreciate if anyone can help in this. 

 

 

 

Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, hkhan 

    If you mean to filter the data of your table, you can try  visual filter pane.

    You can create a seperate calendar table and add measures as below:

     

    Slicerdate = MAX('Calendar'[Date])
    TableDate = MAX('Table'[Date]) 
    diff = DATEDIFF([TableDate],[Slicerdate],MONTH) 
    visual filter measure = 
IF (
    ISFILTERED ( 'Calendar'[Date].[Month] ),
    IF ( [diff] >= 0 && [diff] < 12, 1, 0 ),
    1
)

     

    Then apply the measure 'Visual filter' to 'Filters on this visual'.

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi hkhan 

     

    You can use SAMEPERIODLASTYEAR DAX function.

     

    Prior rolling 12 months = CALCULATE([yourmeasure], SAMEPERIODLASTYEAR( Table[Date]))

     

    or using this filter:

    DATESINPERIOD(dDate[Date],MAX(dDate[Date]),-1,YEAR)

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

     

  • hkhan's avatar
    hkhan
    New Member
    4 years ago

    amitchandak any thoughts on this! I can share my pbix file to explain what I'm trying to acheive 

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      4 years ago

      Hi, hkhan 

      If you mean to filter the data of your table, you can try  visual filter pane.

      You can create a seperate calendar table and add measures as below:

       

      Slicerdate = MAX('Calendar'[Date])
      TableDate = MAX('Table'[Date]) 
      diff = DATEDIFF([TableDate],[Slicerdate],MONTH) 
      visual filter measure = 
IF (
    ISFILTERED ( 'Calendar'[Date].[Month] ),
    IF ( [diff] >= 0 && [diff] < 12, 1, 0 ),
    1
)

       

      Then apply the measure 'Visual filter' to 'Filters on this visual'.

      Best Regards,
      Community Support Team _ Eason
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.