I need a filter that tracks with time

[cnpdx]

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!

[richbenmintz]

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]

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 · Accepted solution]

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

[cnpdx]

This 12 month date filter that "tracks with time" (prior 12 months)works great. How would I modify it to only show data through the end of LAST month (and prior 11 months)? Currently it's showing data through the end of CURRENT month. Here is the current syntax:

XXLAST 12 MONTHS = VAR TodayMonthIndex =
CALCULATE (
MAX ( '_DATE TABLE CUSTOM'[XXMONTHINDEX]),
FILTER( '_DATE TABLE CUSTOM', TODAY() = '_DATE TABLE CUSTOM'[DATE] )
)

VAR monthtocheck = '_DATE TABLE CUSTOM'[XXMONTHINDEX]

RETURN
IF (
AND ( monthtocheck >= TodayMonthIndex - 12, monthtocheck <= TodayMonthIndex ),
1,
0
)

XXMONTHINDEX =
VAR MonthRow = '_DATE TABLE CUSTOM'[XXYEARMONTH]
RETURN
CALCULATE (
DISTINCTCOUNT ('_DATE TABLE CUSTOM'[XXYEARMONTH] ),
FILTER('_DATE TABLE CUSTOM','_DATE TABLE CUSTOM'[XXYEARMONTH] <= MonthRow )
)

XXYEARMONTH = ('_DATE TABLE CUSTOM'[YEAR]*100)+MONTH('_DATE TABLE CUSTOM'[DATE])

[tringuyenminh92]

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]

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]

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]) ) 

[BhaveshPatel]

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.