I would like to develop a measurement, or a report level filter, that always makes my reports show only the current month and the prior 11 months. So it tracks with time, and I don't have to update the filter on a monthly basis. The hack way to do this would be to put the date field into the report level filter, and update the criteria every month. But I need to be more automated than that. is there any way to do this? thank you for your help!

Hi cnpdx, In your scenario, you can create calculated columns in the Date table using the following formulas. 1. Right click your date table and choose “New Column”, paste the following code to create the YearMonth calculated column. YearMonth = if(MONTH('Date'[Date])=MONTH(NOW()),"Current Month",Format('Date'[Date], "YYYY mmmm")) 2. Use the following formula to create the Date Periods calculated column. Date Periods = VAR Datediff = 1 * ( 'Date'[Date]- TODAY () ) RETURN SWITCH ( TRUE, AND ( Datediff <= 0,Datediff >= -330 ), "Prior 11 Months", Datediff < 330, "Older than 11 Months" ) 3. Use the above calculated columns in slicer to filter your visuals. Thanks, Lydia Zhang

I need a filter that tracks with time | Microsoft Fabric Community

8 Replies

BhaveshPatel
Super User
9 years ago
You can try something like this mentioned in this blog. or You can use parameters with a default value of current month.

Check out at HERE.
tringuyenminh92
Memorable Member
9 years ago
Hi cnpdx,

There are some approaches to achieve your expectation, so I just show one of them by Query Editior.

Choose Edit Queries -> Choose your date column and ensure that its type is Date or Datetime

Choose Filter icon in header of date column -> Date Filters -> Custom Filter

Select is "in the previous months" -> 11 -> months
Select or condition and is in month -> this month

Now you have filter condition as your expectation when loading data. Please feel free to play with Power Query for more advanced custom by clicking Advanced Editor and modify the code

If this works for you please accept it as solution and also like to give KUDOS.
- cnpdx
  Helper II
  9 years ago
  I like this solution and have considered it before. You and I think alike. But there is one hurdle that I have to get over first in order to complete your method:
  
  The Sales data table that i would be filtering does not have actual dates, it has date id's linked to a date table. :(
  
  So I wouldn't be able to filter by date until I was able to pull in the actual dates to that table in a new column. Do you know how I would do that IN THE QUERY EDITOR?
  
  Here is a screenshot:
  - tringuyenminh92
    Memorable Member
    9 years ago
    Hi cnpdx,
    
    Doing above filter steps for Dates table
    Making relationship between your Dates table and Fact
    Using Date column in Dates table, it will filter the date in range for you
    But I'm thinking the way to directly filter your Fact table, it will reduce size of loading data. it's better performance.
    Cause your date id is integer, are they increment number or not?
    if yes. you could get that min and max id of min&max date after filter steps and assign to 2 temp variables. Then filtering in Fact by them. (List.Max & List.Min with Table.Column)
    if no, you could check current date id in fact belong to List.Contains of Date column of Dates table >> Table.SelectRows(FactTable, each List.Contains(yourfiltereddateIDsinlist, [LinkToTxnDateID]) )
richbenmintz
Resident Rockstar
9 years ago
If you are using a date table, you can add two additional columns, CurrentMonth and RollingTwelve, then set the values to true or false on each load. you can then filter your reports or visuals by these columns. each refresh will update the visual with the appropriate periods
- cnpdx
  Helper II
  9 years ago
  richbenmintz ,
  
  this solution seems interesting. can you give me a bit more detail on the formulas used and the steps? i am not sure how to implement
  thank you1
  - Anonymous
    Not applicable
    9 years ago
    Hi cnpdx,
    
    In your scenario, you can create calculated columns in the Date table using the following formulas.
    
    1. Right click your date table and choose “New Column”, paste the following code to create the YearMonth calculated column.
    
    YearMonth = if(MONTH('Date'[Date])=MONTH(NOW()),"Current Month",Format('Date'[Date], "YYYY mmmm"))
    
    2. Use the following formula to create the Date Periods calculated column.
    
    Date Periods = VAR Datediff = 1 * ( 'Date'[Date]- TODAY () ) RETURN SWITCH ( TRUE, AND ( Datediff <= 0,Datediff >= -330 ), "Prior 11 Months", Datediff < 330, "Older than 11 Months" )
    
    3. Use the above calculated columns in slicer to filter your visuals.
    
    Thanks,
    Lydia Zhang