Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
djoellet
Frequent Visitor

TTM Calculation for past 3 years

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 !

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @djoellet ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1672897109384.png

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

vbinbinyumsft_1-1672897182633.png

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @djoellet ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1672897109384.png

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

vbinbinyumsft_1-1672897182633.png

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.

amitchandak
Super User
Super User

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

 

 

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.