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

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.

Reply
HamidBee
Impactful Individual
Impactful Individual

Is -1 in DATEADD(.......,-1,YEAR) minus one year from the highest year?

Assum I have data spanning from 2010 to 2020. I'd like to get the total sum for the year 2019. Would I use DATEADD(........,-1,YEAR)?. 

 

When you apply a number in the date add function is that number relative from the maximum year that you are working with?

 

In this case if the maximum year is 2020.

I wrote the following code:

 

Total (DATEADD) = CALCULATE([Total],DATEADD('Calendar'[Date],-1,YEAR))

What it ended up doing is shifting the values by 1 year:
 
HamidBee_0-1643713889705.png

 

Any ideas what I've done wrong here? 
 
I'm sharing the pbix file incase anyone would like to have a look.

https://www.mediafire.com/file/6yljtvgx8vw8mfa/DATEADD.pbix/file
 
Thanks in advance.

 

1 ACCEPTED SOLUTION

I didn't see any issue with the DATEADD measure.

Here's the useful reference to the function,

DATEADD – DAX Guide

 

Pay special attention to the following remarks,

A reference to a date/time column. Only in this case a context transition applies because the <Dates> column reference is replaced by
CALCULATETABLE ( DISTINCT ( <Dates> ) )

furthermore, a simple means to demystify what's exactly happening in the transformaiton mentioned above,

CNENFRNL_0-1643716148115.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

6 REPLIES 6
Tahreem24
Super User
Super User

@HamidBee , Use MAX(Table[YearColumn])-1 inside DATEADD function.

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
HamidBee
Impactful Individual
Impactful Individual

Okay but if I don't use the function MAX, what year would -1 subtract from?

Seems you don't have a fundamental comprehension of concept "Evaluation Context" in DAX. All functions evaluate in specific context, for instance, in a cell of matrix; time intelligence funcs are no exception.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

HamidBee
Impactful Individual
Impactful Individual

I'm definitely trying to get my head around it. I've started to play around with it just to try and see how it works. 

 

I wrote the following code:

 

Total (DATEADD) = CALCULATE([Total],DATEADD('Calendar'[Date],-1,YEAR))

What it ended up doing is shifting the values by 1 year:
 
Shift.png
Any ideas what I've done wrong here? 
 
I'm sharing the pbix file incase anyone would like to have a look.

https://www.mediafire.com/file/6yljtvgx8vw8mfa/DATEADD.pbix/file
 
Thanks in advance.

I didn't see any issue with the DATEADD measure.

Here's the useful reference to the function,

DATEADD – DAX Guide

 

Pay special attention to the following remarks,

A reference to a date/time column. Only in this case a context transition applies because the <Dates> column reference is replaced by
CALCULATETABLE ( DISTINCT ( <Dates> ) )

furthermore, a simple means to demystify what's exactly happening in the transformaiton mentioned above,

CNENFRNL_0-1643716148115.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

mh2587
Super User
Super User

Yes you can use this logic as well


Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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