Dynamic timetable

[paolomint]

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 

[vanessafvg]

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?

[MFelix · Accepted solution]

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