Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Analisys Date = IF( Year(Calendar[Date]) >= Year(Today) - 2 && Year(Calendar[Date]) <= Year(Today) ), 1)
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]))
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshow 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?
Proud to be a Super User!
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.
Analisys Date = IF( Year(Calendar[Date]) >= Year(Today) - 2 && Year(Calendar[Date]) <= Year(Today) ), 1)
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]))
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |