Reply
RichardS
New Member

Significant anniversaries

I am new to the Board so if this has been covered elsewhere, apologies.....

 

I am trying to get a report to show upcoming anniversaries of service, flagging specifically significant ones such as 10th, 21st, 25th etc.  I can get the upcoming anniversaries using a date column from Excel and calculating in a table using DAX the number of years passed.  I have added a table of the "key" years but I cannot think how to link that to the years calculated in the table.  My alternative was to calculate the number of years in the query but I cannot see an equivalent to the DAX "TODAY()" function.  Please can anyone help?

 

Thanks

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

the M funcitons that may help you are:

https://msdn.microsoft.com/en-us/query-bi/m/datetime-localnow

https://msdn.microsoft.com/en-us/query-bi/m/datetime-fixedlocalnow

they return DateTime type so you may want to convert it to Date type
https://msdn.microsoft.com/en-us/query-bi/m/datetime-date

 

I'm not clear on how you want to merge the tables - is it with simple join?

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Perhaps something like this:

 

Column = IF([Years]) IN 'KeyYears'[Years],"Important","Still important but not really all that important")


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Stachu
Community Champion
Community Champion

the M funcitons that may help you are:

https://msdn.microsoft.com/en-us/query-bi/m/datetime-localnow

https://msdn.microsoft.com/en-us/query-bi/m/datetime-fixedlocalnow

they return DateTime type so you may want to convert it to Date type
https://msdn.microsoft.com/en-us/query-bi/m/datetime-date

 

I'm not clear on how you want to merge the tables - is it with simple join?

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Got it, thanks

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)