Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi there,
I have three years of data, and I need to add a conditional column based on [Date] that will return a TTM period so I can filter. I want TTM current year (CY), TTM prior year (PY), and TTM (2Y) two years ago. And if possible, I would like it to be dynamic so that it will update. I have tried a couple of different formulas but without success.
So the periods would look like this:
CY = TTM Current Year - January 1, 2022 - December 31, 2022
PY = TTM Last Year - January 1, 2021 - December 31, 2021
2Y = TTM Two Years - January 1, 2020 - December 31, 2020
Thanks for reading and any assistance !
Solved! Go to Solution.
Hi @djoellet ,
Please try below steps:
1. below is my test table
Table:
2. add a new column with below dax formula
TTM =
VAR _date = [Date]
VAR cur_date =
TODAY ()
RETURN
SWITCH (
TRUE (),
YEAR ( _date ) = YEAR ( cur_date ), "CY",
YEAR ( _date )
= YEAR ( cur_date ) - 1, "PY",
YEAR ( _date )
= YEAR ( cur_date ) - 2, "2Y"
)
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @djoellet ,
Please try below steps:
1. below is my test table
Table:
2. add a new column with below dax formula
TTM =
VAR _date = [Date]
VAR cur_date =
TODAY ()
RETURN
SWITCH (
TRUE (),
YEAR ( _date ) = YEAR ( cur_date ), "CY",
YEAR ( _date )
= YEAR ( cur_date ) - 1, "PY",
YEAR ( _date )
= YEAR ( cur_date ) - 2, "2Y"
)
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@djoellet , Logic based on today
This year Today =
var _min = eomonth(today(),-1*month(today()))+1
var _max = eomonth(_min,12 ,-1*month(today()) )
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
Last year Today =
var _max = eomonth(today(),-1*month(today()))
var _min = eomonth(_max,-12)+1
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
2nd Last year Today =
var _max = eomonth(today(),(-1*month(today()))-12)
var _min = eomonth(_max,-12)+1
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
All About Time Intelligence around Today: https://youtu.be/gcLhhxhXKEI
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.