Forum Discussion
Dynamic timetable
Dear all,
my datetable go from 2012 to 2030.
All the analysis are based on last 3 years (2021-2020-2019).
I'd like to build a model where people should not select the period (for example next year they need to set 2022-2021-2020).
I thought to build another timetable only for these analysis.
For this year 2021 = it start from 01.01.2019 - to 31.12.2021,
automatically on 01.01.2021 it should change in 01.01.2020 to 31.12.2022.
How can I do that?
In your opinion it could be an efficient solution or there are other better ways to manage this issue?
Thank in advance
Paolo
Hi paolomint ,
Don't know how you have your timetable setup and if it's DAX or M language, however and depending on the way you want to do have the data you can do it in several manners.
- If you want to have all you data in the model and "force" people to see the last 3 years you can add a filter to the report with that:
- Another way is to create a column on your calendar table similar to this one and use this column to filter out the data you need in the report filter.
Analisys Date = IF( Year(Calendar[Date]) >= Year(Today) - 2 && Year(Calendar[Date]) <= Year(Today) ), 1)- Other option is to change your calendar table adding the following:
DAX CALENDAR TABLE= FILTER ( CALENDAR ( "01/01/2012", "31/12/2025" ), YEAR ( [Date] ) >= YEAR ( TODAY () ) - 2 && YEAR ( [Date] ) <= YEAR ( TODAY () ) )The filter expression should wrap your calendar syntax
If on the query editor go to your date column and add a filter with the following syntax:
= Table.SelectRows(#"Changed Type2", each Date.IsInPreviousNYears([Date], 2) or Date.IsInCurrentYear([Date]))
2 Replies
Hi paolomint ,
Don't know how you have your timetable setup and if it's DAX or M language, however and depending on the way you want to do have the data you can do it in several manners.
- If you want to have all you data in the model and "force" people to see the last 3 years you can add a filter to the report with that:
- Another way is to create a column on your calendar table similar to this one and use this column to filter out the data you need in the report filter.
Analisys Date = IF( Year(Calendar[Date]) >= Year(Today) - 2 && Year(Calendar[Date]) <= Year(Today) ), 1)- Other option is to change your calendar table adding the following:
DAX CALENDAR TABLE= FILTER ( CALENDAR ( "01/01/2012", "31/12/2025" ), YEAR ( [Date] ) >= YEAR ( TODAY () ) - 2 && YEAR ( [Date] ) <= YEAR ( TODAY () ) )The filter expression should wrap your calendar syntax
If on the query editor go to your date column and add a filter with the following syntax:
= Table.SelectRows(#"Changed Type2", each Date.IsInPreviousNYears([Date], 2) or Date.IsInCurrentYear([Date]))how do you currently create your table? you can probably filter out dynamically the dates you dont want, or if you are pulling from sql just write a dynamic query.
In your text filters in power query it should allow you to do relative filtering. But maybe if you can explain a little more on how you are creating this table currently where is the source?
